Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

DBMS_Describe Package Oracle Tips by Burleson

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.
   

 
               
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.