High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

DBMS_Describe

The DBMS_Describe package contains a single procedure, Describe_Procedure() , which returns information about the parameters of stored procedures and functions. The Describe_Procedure() procedure has 15 separate parameters, as shown in the following definition:

PROCEDURE DBMS_Describe (object_name IN varchar2, reserved1 IN varchar2, reserved2 IN varchar2, overload OUT number_table, position OUT number_table, level OUT number_table, argument_name OUT varchar2_table, datatype OUT number_table, default_value OUT number_table, in_out OUT number_table, length OUT number_table, precision OUT number_table, scale OUT number_table, radix OUT number_table, spare OUT number_table)

The object_name parameter identifies the procedure or function that the DBMS_Describe() procedure should investigate. The reserved1 and reserved2 parameters aren t currently used and should be passed as NULL values.

The remaining parameters are PL/SQL tables that hold information about the parameters:

    overload ”Holds an integer value that indicates to which overloaded procedure or function the parameter corresponds. For instance, a function might be overloaded three times, so the parameter might contain the values 0, 1, and 2.

    position ”Holds an integer value that indicates the position of a parameter with the argument list for the object. Position 0 is reserved for a function s return value.

    level ”Indicates how deep an individual parameter is nested.

    argument_name ”Indicates the name of an individual parameter.

    datatype ”Holds an integer value that indicates the datatype of an individual parameter. A complete list of these values can be found in Table 9.1.

    default_value ”Holds the given default for a parameter.

    in_out ”Indicates an integer value. 0 indicates the parameter is an IN parameter, 1 means the parameter is an OUT parameter, and 2 means the parameter is an IN OUT parameter.

    length ”Indicates the length of varchar2 or char arguments.

    precision ”Indicates the number of significant digits for a numeric parameter.

    scale ”Indicates the number of significant digits beyond the decimal point for a numeric parameter.

    radix ”Indicates the base of a numeric value (decimal, binary, octal, and so forth).

    spare ”Is not used.

DBMS_Job

The DBMS_Job package allows developers to schedule execution of PL/SQL code at a later time. Using the DBMS_Job package requires your DBA to set up some parameters in the init.ora file; consult with your DBA to determine if the database is set up for the use of this package.

The package contains the following 10 procedures:

   The Broken() procedure

   The Change() procedure

   The Interval() procedure

   The ISubmit() procedure

   The Next_Date() procedure

   The Remove() procedure

   The Run() procedure

   The Submit() procedure

   The User_Export() procedure

   The What() procedure

The simplest sequence of events for running a job is very straightforward. The developer calls the Submit() procedure. The developer doesn t have to do any further tasks . At the scheduled time, Oracle will execute the specified job.

Unfortunately, things don t always work as expected. For instance, a job becomes broken if an error occurs while the job is executing. It s possible that a job that has already been submitted needs to be altered or canceled entirely. When these things happen, the other procedures within the package come into play.

The Broken() Procedure

The Broken() procedure is used to update the status of a job that has already been submitted, typically to mark a broken job as unbroken. The procedure has three parameters: job , broken , and next_date .

PROCEDURE Broken (job IN binary_integer, broken IN boolean, next_date IN date := SYSDATE)

The job parameter is the job number that uniquely identifies the job in question. The broken parameter indicates whether or not the job will be marked as broken ” TRUE means that the job will be marked as broken, and FALSE means that the job will be marked as unbroken. The next_date parameter indicates the time at which the job will be run again. This parameter defaults to the current date and time.

The Change() Procedure

The Change() procedure is used to alter the settings for a specific job. The procedure has four parameters: job , what , next_date , and interval .

PROCEDURE Change (job IN binary_integer, what IN varchar2, next_date IN date, interval IN varchar2)

Once again, the job parameter is the integer value that uniquely identifies the job. The what parameter is a block of PL/SQL code that is to be run by the job. The next_date parameter indicates when the job will be executed. The interval parameter indicates how often a job will be re-executed.