14.1 Packages (Oracle Only)

On This Page:

spdb_triggers
spdb_audit
spdb_eff_date
spdb_tidy
spdb_utils

spdb_triggers

The procedures in this package are called from database triggers and should not be called directly by any application.

Procedure	Called from...
`spdb_triggers.sp083_validation`	Procedure called from trigger `sp083_validation`.
`spdb_triggers.sp100_validation`	Procedure called from trigger `sp100_validation`.
`spdb_triggers.sp108_set_defaults`	Procedure called from trigger `sp108_set_defaults`.

spdb_audit

The procedures in this package are called from database triggers and should not be called directly by any application

Procedure	Called from...
`spdb_audit.audit_sp083`	Procedure called from trigger `sp083_audit`.
`spdb_audit.audit_sp100`	Procedure called from trigger `sp100_audit`.
`spdb_audit.audit_sp108`	Procedure called from trigger `sp108_audit`.
`spdb_audit.audit_sp111`	Procedure called from trigger `sp111_audit`.
`spdb_audit.audit_sp213`	Procedure called from trigger `sp213_audit`.

spdb_eff_date

This package contains the function sp_eff_date.

Function	Parameters	Description
`spdb_eff_date.sp_eff_date`	None	This function returns a date value. If the ServiceOptimizer Effective Date has been set in the database (parameter `eff_date` in table `sp083_system_parameters`), then the function returns that date and the current time as returned from `sysdate`. If the ServiceOptimizer Effective Date has not been set, the function simply returns `sysdate`.

Standalone function sp_eff_date

A standalone function (i.e. one not contained within any package), also called sp_eff_date, is provided for ease of use; this standalone function simply returns the value returned by the packaged function.

The sp_eff_date function may be used in SQL queries wherever sysdate might be used. However, care should be exercised when using the function - unlike sysdate, sp_eff_date is evaluated once for every row referred to in a query (sysdate is evaluated only once for the entire query).

The query:

select distinct sysdate from big_table; select distinct sysdate from big_table;

where big_table contains many records, will only return a single value, whereas the query:

select sp_eff_date from big_table;

May return multiple values.

Moreover, the query:

select 1 from dual where sp_eff_date < sp_eff_date;

may occasionally return a row!

spdb_tidy

This package has been replaced by the standalone utility sp_dbtidy.

This utility tidies data by group. There are 3 groups Global, Audit and Job. See the table descriptions to find which tables are tidied in a particular group.

For further usage of the sp_dbtidy utility refer to the System Administrator's Guide.

spdb_utils

This package provides general-purpose utilities.

Function Parameters Description

spdb_utils.set_null_date(the_date in out date)

the_date

date and time to be set

By convention, date columns which are used to store times - i.e. where the date part is of no interest - have the date set to 1/1/1970. This procedure can be used to such effect.

spdb_utils.sp_error(error_no in number)

error_no

positive integer in the range 20000 to 20999 inclusive.

Maps ServiceScheduling error number to error text, then calls raise application error.