Document toolboxDocument toolbox

Packages (Oracle Only)

spdb_triggers

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

ProcedureCalled 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
ProcedureCalled 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.

FunctionParametersDescription

spdb_eff_date.sp_eff_date

NoneThis 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.

FunctionParametersDescription

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.


Â