You can use the DBMS_DESCRIBE package 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 also checked on the final object.
This chapter contains the following topics:
Overview
Security Model
Types
Exceptions
Examples
This package provides the same functionality as the Oracle Call Interface OCIDescribeAny call.
See Also:
Oracle Call Interface Programmer's GuideThis package is available to PUBLIC and performs its own security checking based on the schema object being described.
The DBMS_DESCRIBE package declares two PL/SQL table types, which are used to hold data returned by DESCRIBE_PROCEDURE in its OUT parameters. The types are:
TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
TYPE NUMBER_TABLE IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
DBMS_DESCRIBE can raise application errors in the range -20000 to -20004.
Table 47-1 DBMS_DESCRIBE Errors
| Error | Description |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Syntax error attempting to parse ' |
One use of the DESCRIBE_PROCEDURE procedure is as an external service interface.
For example, consider a client that provides an OBJECT_NAME of SCOTT.ACCOUNT_UPDATE, where ACCOUNT_UPDATE is an overloaded function with specification:
TABLE account (account_no NUMBER, person_id NUMBER,
balance NUMBER(7,2))
TABLE person (person_id number(4), person_nm varchar2(10))
FUNCTION ACCOUNT_UPDATE (account_no NUMBER,
person person%rowtype,
amounts DBMS_DESCRIBE.NUMBER_TABLE,
trans_date DATE)
return account.balance%type;
FUNCTION ACCOUNT_UPDATE (account_no NUMBER,
person person%rowtype,
amounts DBMS_DESCRIBE.NUMBER_TABLE,
trans_no NUMBER)
return account.balance%type;
This procedure might look similar to the following output:
overload position argument level datatype length prec scale rad
-------- --------- -------- ------ -------- ------ ---- ----- ---
1 0 0 2 22 7 2 10
1 1 ACCOUNT 0 2 0 0 0 0
1 2 PERSON 0 250 0 0 0 0
1 1 PERSON_ID 1 2 22 4 0 10
1 2 PERSON_NM 1 1 10 0 0 0
1 3 AMOUNTS 0 251 0 0 0 0
1 1 1 2 22 0 0 0
1 4 TRANS_DATE 0 12 0 0 0 0
2 0 0 2 22 7 2 10
2 1 ACCOUNT_NO 0 2 22 0 0 0
2 2 PERSON 0 2 22 4 0 10
2 3 AMOUNTS 0 251 22 4 0 10
2 1 1 2 0 0 0 0
2 4 TRANS_NO 0 2 0 0 0 0
The following PL/SQL procedure has as its parameters all of the PL/SQL datatypes:
CREATE OR REPLACE PROCEDURE p1 (
pvc2 IN VARCHAR2,
pvc OUT VARCHAR,
pstr IN OUT STRING,
plong IN LONG,
prowid IN ROWID,
pchara IN CHARACTER,
pchar IN CHAR,
praw IN RAW,
plraw IN LONG RAW,
pbinint IN BINARY_INTEGER,
pplsint IN PLS_INTEGER,
pbool IN BOOLEAN,
pnat IN NATURAL,
ppos IN POSITIVE,
pposn IN POSITIVEN,
pnatn IN NATURALN,
pnum IN NUMBER,
pintgr IN INTEGER,
pint IN INT,
psmall IN SMALLINT,
pdec IN DECIMAL,
preal IN REAL,
pfloat IN FLOAT,
pnumer IN NUMERIC,
pdp IN DOUBLE PRECISION,
pdate IN DATE,
pmls IN MLSLABEL) AS
BEGIN
NULL;
END;
If you describe this procedure using the following:
CREATE OR REPLACE PACKAGE describe_it AS
PROCEDURE desc_proc (name VARCHAR2);
END describe_it;
CREATE OR REPLACE PACKAGE BODY describe_it AS
PROCEDURE prt_value(val VARCHAR2, isize INTEGER) IS
n INTEGER;
BEGIN
n := isize - LENGTHB(val);
IF n < 0 THEN
n := 0;
END IF;
DBMS_OUTPUT.PUT(val);
FOR i in 1..n LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
END prt_value;
PROCEDURE desc_proc (name VARCHAR2) IS
overload DBMS_DESCRIBE.NUMBER_TABLE;
position DBMS_DESCRIBE.NUMBER_TABLE;
c_level DBMS_DESCRIBE.NUMBER_TABLE;
arg_name DBMS_DESCRIBE.VARCHAR2_TABLE;
dty DBMS_DESCRIBE.NUMBER_TABLE;
def_val DBMS_DESCRIBE.NUMBER_TABLE;
p_mode DBMS_DESCRIBE.NUMBER_TABLE;
length DBMS_DESCRIBE.NUMBER_TABLE;
precision DBMS_DESCRIBE.NUMBER_TABLE;
scale DBMS_DESCRIBE.NUMBER_TABLE;
radix DBMS_DESCRIBE.NUMBER_TABLE;
spare DBMS_DESCRIBE.NUMBER_TABLE;
idx INTEGER := 0;
BEGIN
DBMS_DESCRIBE.DESCRIBE_PROCEDURE(
name,
null,
null,
overload,
position,
c_level,
arg_name,
dty,
def_val,
p_mode,
length,
precision,
scale,
radix,
spare);
DBMS_OUTPUT.PUT_LINE('Position Name DTY Mode');
LOOP
idx := idx + 1;
prt_value(TO_CHAR(position(idx)), 12);
prt_value(arg_name(idx), 12);
prt_value(TO_CHAR(dty(idx)), 5);
prt_value(TO_CHAR(p_mode(idx)), 5);
DBMS_OUTPUT.NEW_LINE;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.NEW_LINE;
END desc_proc;
END describe_it;
Then the results list all the numeric codes for the PL/SQL datatypes:
Position Name Datatype_Code Mode 1 PVC2 1 0 2 PVC 1 1 3 PSTR 1 2 4 PLONG 8 0 5 PROWID 11 0 6 PCHARA 96 0 7 PCHAR 96 0 8 PRAW 23 0 9 PLRAW 24 0 10 PBININT 3 0 11 PPLSINT 3 0 12 PBOOL 252 0 13 PNAT 3 0 14 PPOS 3 0 15 PPOSN 3 0 16 PNATN 3 0 17 PNUM 2 0 18 PINTGR 2 0 19 PINT 2 0 20 PSMALL 2 0 21 PDEC 2 0 22 PREAL 2 0 23 PFLOAT 2 0 24 PNUMER 2 0 25 PDP 2 0 26 PDATE 12 0 27 PMLS 106 0
Table 47-2 DBMS_DESCRIBE Package Subprograms
| Subprogram | Description |
|---|---|
|
Provides a brief description of a PL/SQL stored procedure |
The procedure DESCRIBE_PROCEDURE provides a brief description of a PL/SQL stored procedure. It takes the name of a stored procedure and returns information about each parameter of that procedure.
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 DEFAULT FALSE);
Table 47-3 DBMS_DESCRIBE.DESCRIBE_PROCEDURE Parameters
| Parameter | Description |
|---|---|
|
|
Name of the procedure being described. The syntax for this parameter follows the rules used for identifiers in SQL. The name can be a synonym. This parameter is required and may not be null. The total length of the name cannot exceed 197 bytes. An incorrectly specified
|
|
|
Reserved for future use -- must be set to |
|
|
A unique number assigned to the procedure's signature. If a procedure is overloaded, then this field holds a different value for each version of the procedure. |
|
|
Position of the argument in the parameter list. Position 0 returns the values for the return type of a function. |
|
|
If the argument is a composite type, such as record, then this parameter returns the level of the datatype. See the Oracle Call Interface Programmer's Guide for a description of the |
|
|
Name of the argument associated with the procedure that you are describing. |
|
|
Oracle datatype of the argument being described. The datatypes and their numeric type codes are: 0 placeholder for procedures with no arguments 1 VARCHAR, VARCHAR, STRING 2 NUMBER, INTEGER, SMALLINT, REAL, FLOAT, DECIMAL 3 BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL 8 LONG 11 ROWID 12 DATE 23 RAW 24 LONG RAW 58 OPAQUE TYPE 96 CHAR (ANSI FIXED CHAR), CHARACTER 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 |
|
|
1 if the argument being described has a default value; otherwise, the value is 0. |
|
|
Describes the mode of the parameter: 0 IN 1 OUT 2 IN OUT |
|
|
For |
|
|
If the argument being described is of datatype 2 ( |
|
|
If the argument being described is of datatype 2 ( |
|
|
If the argument being described is of datatype 2 ( |
|
|
Reserved for future functionality. |
|
|
The default is |
All values from DESCRIBE_PROCEDURE are returned in its OUT parameters. The datatypes for these are PL/SQL tables, to accommodate a variable number of parameters.