Oracle DBMS_DESCRIBE
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
ARGUMENT$ DBMS_UTILITY OBJ$
DBMS_STANDARD NLS_DATABASE_PARAMETERS  
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;
/

Related Topics
Data Types & Subtypes
Packages
Procedures

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved