11
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 package provides the same functionality as the Oracle Call Interface OCIDescribeAny call.

Security

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;

Errors

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

Table 11-1 DBMS_DESCRIBE Errors

ORA-20000

ORA-20001

ORA-20002

ORA-20003

ORA-20004

Summary of Subprograms

DBMS_DESCRIBE contains only one procedure: DESCRIBE_PROCEDURE.

DESCRIBE_PROCEDURE procedure

The procedure DESCRIBE_PROCEDURE accepts the name of a stored procedure, a description of the procedure, and each of its parameters.

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); 

Parameters

Table 11-2 DBMS_DESCRIBE.DESCRIBE_PROCEDURE Parameters

object_name

reserved1 
reserved2

overload

position

level

argument_name

datatype

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
96  CHAR (ANSI FIXED CHAR), CHARACTER
106 MLSLABEL
250 PL/SQL RECORD
251 PL/SQL TABLE
252 PL/SQL BOOLEAN

default_value

in_out

0 IN
1 OUT
2 IN OUT

length

precision

scale

radix

spare

Return Values

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

Examples

One use of the DESCRIBE_PROCEDURE procedure would be 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       accounts.balance%type;

function ACCOUNT_UPDATE (account_no   number, 
                         person       person%rowtype,
                         amounts      dbms_describe.number_table, 
                         trans_no     number)
                         return       accounts.balance%type;

The describe of this procedure might look similar to the output shown below.

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 package below:

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, as shown below, 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

Usage Notes

There is currently no way from a third generation language to directly bind to an argument of type record or boolean. For Booleans, there are the following work-arounds:

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

    begin :dtyint_bind_var := to_number(f); end;
      
    
        begin g(to_boolean(:dtyint_bind_var)); end;
      
    
        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 type record require writting a wrapper similar to that in the last example above (see funciton H).