| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsdesc.sql |
| First Available |
7.3.4 |
| 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$ |
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' |
|
| Security Model |
Execute is granted to PUBLIC |
| |
| 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;
/ |
|
|
