Skip Headers

PL/SQL Packages and Types Reference
10g Release 1 (10.1)

Part Number B10802-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

31
DBMS_DESCRIBE

You can use the DBMS_DESCRIBE package to get information about a PL/SQL object. When you specify an object name, DBMS_DESCRIBE returns a set of indexed tables with the results. Full name translation is performed and security checking is also checked on the final object.

This chapter contains the following topics:


Using DBMS_DESCRIBE


Overview

This package provides the same functionality as the Oracle Call Interface OCIDescribeAny call.

See Also:

Oracle Call Interface Programmer's Guide


Security Model

This package is available to PUBLIC and performs its own security checking based on the schema object being described.


Types

The DBMS_DESCRIBE package declares two PL/SQL table types, which are used to hold data returned by DESCRIBE_PROCEDURE in its OUT parameters. The types are:

TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(30)
 INDEX BY BINARY_INTEGER;

TYPE NUMBER_TABLE IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;

Exceptions

DBMS_DESCRIBE can raise application errors in the range -20000 to -20004.

Table 31-1  DBMS_DESCRIBE Errors
Error Description
ORA-20000

ORU 10035: cannot describe a package ('X') only a procedure within a package.

ORA-20001

ORU-10032: procedure 'X' within package 'Y' does not exist.

ORA-20002

ORU-10033: object 'X' is remote, cannot describe; expanded name 'Y'.

ORA-20003

ORU-10036: object 'X' is invalid and cannot be described.

ORA-20004

Syntax error attempting to parse 'X'.


Operational Notes

From a third generation language, we cannot directly bind to an argument of record or boolean type. For a Boolean, the following workaround is available:

Assume function F returns a Boolean. G is a procedure with one IN Boolean argument, and H is a procedure with one OUT Boolean argument. You can execute these functions, binding in DTYINTs (native integer) as follows, where 0=>FALSE and 1=>TRUE:

  DECLARE b BOOLEAN;
  BEGIN h(b); 
     IF b 
         THEN :dtyint_bind_var := 1;
         ELSE :dtyint_bind_var := 0; 
     END IF; 
  END;

Access to procedures with arguments of the record type require writing a wrapper similar to that in the preceding example.


Examples

One use of the DESCRIBE_PROCEDURE procedure is as an external service interface.

For example, consider a client that provides an OBJECT_NAME of SCOTT.ACCOUNT_UPDATE, where ACCOUNT_UPDATE is an overloaded function with specification:

TABLE account (account_no NUMBER, person_id NUMBER,
               balance NUMBER(7,2)) 
TABLE person  (person_id number(4), person_nm varchar2(10))

FUNCTION ACCOUNT_UPDATE (account_no   NUMBER,
                         person       person%rowtype,
                         amounts      DBMS_DESCRIBE.NUMBER_TABLE,
                         trans_date   DATE)
                         return       account.balance%type;

FUNCTION ACCOUNT_UPDATE (account_no   NUMBER, 
                         person       person%rowtype,
                         amounts      DBMS_DESCRIBE.NUMBER_TABLE, 
                         trans_no     NUMBER)
                         return       account.balance%type;

This procedure might look similar to the following output:

overload position  argument level  datatype length prec scale rad  
-------- --------- -------- ------ -------- ------ ---- ----- ---  
       1        0               0         2     22    7     2  10  
       1        1   ACCOUNT     0         2      0    0     0   0  
       1        2   PERSON      0       250      0    0     0   0  
       1        1   PERSON_ID   1         2     22    4     0  10  
       1        2   PERSON_NM   1         1     10    0     0   0  
       1        3   AMOUNTS     0       251      0    0     0   0  
       1        1               1         2     22    0     0   0  
       1        4   TRANS_DATE  0        12      0    0     0   0  
       2        0               0         2     22    7     2  10  
       2        1   ACCOUNT_NO  0         2     22    0     0   0  
       2        2   PERSON      0         2     22    4     0  10  
       2        3   AMOUNTS     0       251     22    4     0  10  
       2        1               1         2      0    0     0   0  
       2        4   TRANS_NO    0         2      0    0     0   0  

The following PL/SQL procedure has as its parameters all of the PL/SQL datatypes:

CREATE OR REPLACE PROCEDURE p1 (
        pvc2    IN     VARCHAR2,
        pvc     OUT    VARCHAR,
        pstr    IN OUT STRING,
        plong   IN     LONG,
        prowid  IN     ROWID,
        pchara  IN     CHARACTER,
        pchar   IN     CHAR,
        praw    IN     RAW,
        plraw   IN     LONG RAW,
        pbinint IN     BINARY_INTEGER,
        pplsint IN     PLS_INTEGER,
        pbool   IN     BOOLEAN,
        pnat    IN     NATURAL,
        ppos    IN     POSITIVE,
        pposn   IN     POSITIVEN,
        pnatn   IN     NATURALN,
        pnum    IN     NUMBER,
        pintgr  IN     INTEGER,
        pint    IN     INT,
        psmall  IN     SMALLINT,
        pdec    IN     DECIMAL,
        preal   IN     REAL,
        pfloat  IN     FLOAT,
        pnumer  IN     NUMERIC,
        pdp     IN     DOUBLE PRECISION,
        pdate   IN     DATE,
        pmls    IN     MLSLABEL) AS

BEGIN
    NULL;
END;

If you describe this procedure using the following:

CREATE OR REPLACE PACKAGE describe_it AS

    PROCEDURE desc_proc (name VARCHAR2);

END describe_it;

CREATE OR REPLACE PACKAGE BODY describe_it AS

  PROCEDURE prt_value(val VARCHAR2, isize INTEGER) IS
    n INTEGER;
  BEGIN
    n := isize - LENGTHB(val);
    IF n < 0 THEN
      n := 0;
    END IF;
    DBMS_OUTPUT.PUT(val);
    FOR i in 1..n LOOP
      DBMS_OUTPUT.PUT(' ');
    END LOOP;
  END prt_value;

  PROCEDURE desc_proc (name VARCHAR2) IS

      overload     DBMS_DESCRIBE.NUMBER_TABLE;
      position     DBMS_DESCRIBE.NUMBER_TABLE;
      c_level      DBMS_DESCRIBE.NUMBER_TABLE;
      arg_name     DBMS_DESCRIBE.VARCHAR2_TABLE;
      dty          DBMS_DESCRIBE.NUMBER_TABLE;
      def_val      DBMS_DESCRIBE.NUMBER_TABLE;
      p_mode       DBMS_DESCRIBE.NUMBER_TABLE;
      length       DBMS_DESCRIBE.NUMBER_TABLE;
      precision    DBMS_DESCRIBE.NUMBER_TABLE;
      scale        DBMS_DESCRIBE.NUMBER_TABLE;
      radix        DBMS_DESCRIBE.NUMBER_TABLE;
      spare        DBMS_DESCRIBE.NUMBER_TABLE;
      idx          INTEGER := 0;
  
  BEGIN
      DBMS_DESCRIBE.DESCRIBE_PROCEDURE(
              name,
              null,
              null,
              overload,
              position,
              c_level,
              arg_name,
              dty,
              def_val,
              p_mode,
              length,
              precision,
              scale,
              radix,
              spare);
  
      DBMS_OUTPUT.PUT_LINE('Position    Name        DTY  Mode');
      LOOP
          idx := idx + 1;
          prt_value(TO_CHAR(position(idx)), 12);
          prt_value(arg_name(idx), 12);
          prt_value(TO_CHAR(dty(idx)), 5);
          prt_value(TO_CHAR(p_mode(idx)), 5);
          DBMS_OUTPUT.NEW_LINE;
      END LOOP;
  EXCEPTION
     WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.NEW_LINE;
        DBMS_OUTPUT.NEW_LINE;

  END desc_proc;
END describe_it;

Then the results list all the numeric codes for the PL/SQL datatypes:

Position  Name    Datatype_Code  Mode
1         PVC2      1              0 
2         PVC       1              1 
3         PSTR      1              2 
4         PLONG     8              0 
5         PROWID    11             0 
6         PCHARA    96             0 
7         PCHAR     96             0 
8         PRAW      23             0 
9         PLRAW     24             0 
10        PBININT   3              0 
11        PPLSINT   3              0 
12        PBOOL     252            0 
13        PNAT      3              0 
14        PPOS      3              0 
15        PPOSN     3              0 
16        PNATN     3              0 
17        PNUM      2              0 
18        PINTGR    2              0 
19        PINT      2              0 
20        PSMALL    2              0 
21        PDEC      2              0 
22        PREAL     2              0 
23        PFLOAT    2              0 
24        PNUMER    2              0 
25        PDP       2              0 
26        PDATE     12             0
27        PMLS      106            0

Summary of DBMS_DESCRIBE Subprograms

Table 31-2  DBMS_DESCRIBE Package Subprograms
Subprogram Description

DESCRIBE_PROCEDURE Procedure

Provides a brief description of a PL/SQL stored procedure


DESCRIBE_PROCEDURE Procedure

The procedure DESCRIBE_PROCEDURE provides a brief description of a PL/SQL stored procedure. It takes the name of a stored procedure and returns information about each parameter of that procedure.

Syntax

DBMS_DESCRIBE.DESCRIBE_PROCEDURE(
   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
   include_string_constraints    OUT BOOLEAN DEFAULT FALSE); 

Parameters

Table 31-3  DBMS_DESCRIBE.DESCRIBE_PROCEDURE Parameters
Parameter Description
object_name

Name of the procedure being described.

The syntax for this parameter follows the rules used for identifiers in SQL. The name can be a synonym. This parameter is required and may not be null. The total length of the name cannot exceed 197 bytes. An incorrectly specified OBJECT_NAME can result in one of the following exceptions:

ORA-20000 - A package was specified. You can only specify a stored procedure, stored function, packaged procedure, or packaged function.

ORA-20001 - The procedure or function that you specified does not exist within the given package.

ORA-20002 - The object that you specified is a remote object. This procedure cannot currently describe remote objects.

ORA-20003 - The object that you specified is invalid and cannot be described.

ORA-20004 - The object was specified with a syntax error.

reserved1 
reserved2

Reserved for future use -- must be set to NULL or the empty string.

overload

A unique number assigned to the procedure's signature.

If a procedure is overloaded, then this field holds a different value for each version of the procedure.

position

Position of the argument in the parameter list.

Position 0 returns the values for the return type of a function.

level

If the argument is a composite type, such as record, then this parameter returns the level of the datatype. See the Oracle Call Interface Programmer's Guide for a description of the ODESSP call for an example.

argument_name

Name of the argument associated with the procedure that you are describing.

datatype

Oracle datatype of the argument being described. The datatypes and their numeric type codes are:

0   placeholder for procedures with no arguments
1   VARCHAR, VARCHAR, STRING
2   NUMBER, INTEGER, SMALLINT, REAL, FLOAT, DECIMAL
3   BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL
8   LONG
11  ROWID
12  DATE
23  RAW
24  LONG RAW
58  OPAQUE TYPE
96  CHAR (ANSI FIXED CHAR), CHARACTER
106 MLSLABEL
121 OBJECT
122 NESTED TABLE
123 VARRAY
178 TIME
179 TIME WITH TIME ZONE
180 TIMESTAMP
181 TIMESTAMP WITH TIME ZONE
231 TIMESTAMP WITH LOCAL TIME ZONE
250 PL/SQL RECORD
251 PL/SQL TABLE
252 PL/SQL BOOLEAN

default_value

1 if the argument being described has a default value; otherwise, the value is 0.

in_out

Describes the mode of the parameter:

0 IN
1 OUT
2 IN OUT

length

For %rowtype formal arguments, the length constraint is returned, otherwise 0 is returned.If the include_string_constraints parameter is set to TRUE, the argument's formal length constraint is passed back if it is of the appropriate type. Those are the string types: 1;8;23;24;96

precision

If the argument being described is of datatype 2 (NUMBER), then this parameter is the precision of that number.

scale

If the argument being described is of datatype 2 (NUMBER), then this parameter is the scale of that number.

radix

If the argument being described is of datatype 2 (NUMBER), then this parameter is the radix of that number.

spare

Reserved for future functionality.

include_string_
constraints

The default is FALSE. If the parameter is set to TRUE, the arguments' formal type constraints is passed back if it is of the appropriate type.Those are the string types: 1;8;23;24;96

Return Values

All values from DESCRIBE_PROCEDURE are returned in its OUT parameters. The datatypes for these are PL/SQL tables, to accommodate a variable number of parameters.