Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

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.
     

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

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

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter