285-288 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

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.

**Table 9.1** Values for the datatype parameter of the **DBMS_Describe.Describe_Procedure()** procedure.

Parameter Value	Datatype
1	varchar2
2	number
3	binary_integer
8	long
11	ROWID
12	date
23	raw
24	long raw
96	char
106	mlslabel
250	PL/SQL record
251	PL/SQL table
252	boolean

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.

Table of Contents