CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 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 [183 users online]    © 2010 psoug.org