Oracle DBMS_DESCRIBE


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_DESCRIBE

Version 11.1

General Information

Source

{ORACLE_HOME}/rdbms/admin/dbmsdesc.sql

First Available

7.3.4

Data Types

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

TYPE NUMBER_TABLE IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;

Dependencies

ARGUMENT$	NLS_DATABASE_PARAMETERS
DBMS_UTILITY	OBJ$

Exceptions

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

Object Privileges

GRANT execute ON dbms_describe TO <schema_name>

GRANT execute ON dbms_describe TO uwclass;

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(type_num) || ' ' || type_desc
INTO td_out
FROM numtype_codes
WHERE 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 type_desc
    INTO dtdesc
    FROM numtype_codes
    WHERE type_num = dtyp(i);

    SELECT DECODE(inout(i), 0, 'IN', 1, 'OUT', 'IN/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

Procedures

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us