Oracle 18cR3 DBMS

Oracle DBMS_DESCRIBE Version 18.3.0.1

General Information

Library Note

Morgan's Library Page Header

For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.

Purpose Used 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 performed on the final object.

AUTHID DEFINER

Data Types TYPE VRCHAR2_TABLE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; TYPE NUMBER_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

Dependencies

ALL_PROCEDURES	DBMS_STANDARD	NLS_DATABASE_PARAMETERS
ARGUMENT$	DBMS_UTILITY	OBJ$

Documented Yes

Exceptions

Error Code	Reason
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'

First Available 7.3.4

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsdesc.sql

DESCRIBE_PROCEDURE

Takes the name of a stored procedure and returns parameter metadata

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 := FALSE);

Data Type Numeric Type Codes
0	placeholder for procedures with no arguments
1	VARCHAR, VARCHAR2, STRING, NVARCHAR2
2	NUMBER, INTEGER, SMALLINT, REAL, FLOAT, DECIMAL
3	BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL
8	LONG
11	ROWID (old)
12	DATE
23	RAW
24	LONG RAW
58	OPAQUE TYPE
69	ROWID (new)
96	CHAR (ANSI FIXED CHAR), NCHAR, CHARACTER
100	BINARY_FLOAT
101	BINARY_DOUBLE
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

CREATE TABLE numtype_codes (

      type_num  NUMBER(3),

      type_desc VARCHAR2(50));

      

      INSERT INTO numtype_codes

      (type_num, type_desc)

      VALUES

      (1, 'VARCHAR, VARCHAR2, STRING, NVARCHAR2');

      

      INSERT INTO numtype_codes

      (type_num, type_desc)

      VALUES

      (2, 'NUMBER, INTEGER, SMALLINT, REAL, FLOAT, DECIMAL');

      COMMIT;

      

      
        
          CREATE OR REPLACE PROCEDURE testproc(tn_in INTEGER, td_out OUT VARCHAR2) IS

            BEGIN

              SELECT TO_CHAR(nc.type_num) || ' ' || nc.type_desc

              INTO td_out

              FROM numtype_codes nc

              WHERE nc.type_num = tn_in;

            END testproc;

            /
        
      
      

      set serveroutput on

DECLARE

           over   dbms_describe.number_table;

           posn   dbms_describe.number_table;

           levl   dbms_describe.number_table;

           arg    dbms_describe.varchar2_table;

           dtyp   dbms_describe.number_table;

           defv   dbms_describe.number_table;

           inout  dbms_describe.number_table;

           len    dbms_describe.number_table;

           prec   dbms_describe.number_table;

           scal   dbms_describe.number_table;

           n      dbms_describe.number_table;

          

           dtdesc numtype_codes.type_desc%TYPE;

           iodesc VARCHAR2(6);

          BEGIN

            dbms_describe.describe_procedure('TESTPROC', NULL, NULL,

            over, posn, levl, arg, dtyp, defv, inout, len, prec, scal, n, n);

          

            FOR i IN 1..over.COUNT LOOP

              SELECT nc.type_desc

              INTO dtdesc

              FROM numtype_codes nc

              WHERE nc.type_num = dtyp(i);

          

              SELECT DECODE(inout(i), 0, 'IN', 1, 'IN-OUT', 'OUT')

              INTO iodesc

              FROM dual;

          

              dbms_output.put_line('Parameter: ' || posn(i));

              dbms_output.put_line('Overload: ' || over(i));

              dbms_output.put_line('Level: ' || levl(i));

              dbms_output.put_line('Argument Name: ' || arg(i));

              dbms_output.put_line('Data Type: ' || dtyp(i)||' '|| dtdesc);

              dbms_output.put_line('Default Value: ' || defv(i));

              dbms_output.put_line('In-Out: ' || inout(i) ||' ' || iodesc);

              dbms_output.put_line('Length: ' || len(i));

              dbms_output.put_line('Precision: ' || prec(i));

              dbms_output.put_line('Scale: ' || scal(i));

              dbms_output.put_line('*********************');

            END LOOP;

          END;

          /