Oracle 12c DBMS_DESCRIBE Demos

Oracle DBMS_DESCRIBE Version 12.1.0.2

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

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