The DBMS_DESCRIBE package is created when the Oracle database is installed. The dbmsdesc.sql script (found in the built-in packages source code directory, as described in Chapter 1 ) contains the source code for this package's specification. This script is called by catproc.sql , which is normally run immediately after database creation. The script creates the public synonym DMS_DESCRIBE for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.
Table 10.2 summarizes the single procedure available through DBMS_DESCRIBE.
In addition to the DESCRIBE_PROCEDURE procedure, DBMS_DESCRIBE defines two PL/SQL table types you can use when calling or describing a PL/SQL object. These are described in the following table.
The two table TYPES are defined as follows:
TYPE DBMS_DESCRIBE.VARCHAR2_TABLE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; TYPE DBMS_DESCRIBE.NUMBER_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
10.2.1.3 The DBMS_DESCRIBE. DESCRIBE_PROCEDURE procedure
The DESCRIBE_PROCEDURE procedure describes the specified PL/SQL object (currently only procedures and functions are supported). It returns information about the parameters of the program in a series of PL/SQL tables. The header for this procedure follows:
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);
Paremeters are summarized in the following table.
DBMS_DESCRIBE.DESCRIBE_PROCEDURE may raise any of the exceptions listed in the following table.
Notice that these exceptions are not defined in the specification of the package. Instead, DESCRIBE_PROCEDURE simply calls RAISE_APPLICATION_ERROR with the error numbers listed earlier. These error numbers may therefore conflict with your own -20NNN error number usages (this is a very bad design decision on Oracle's part). If you embed calls to DESCRIBE_PROCEDURE inside your application or utility, watch out for the confusion such conflicts can cause.
There are several limitations on using DESCRIBE_PROCEDURE:
CREATE TABLE account (account_no number, person_id number, balance number(7,2)); CREATE TABLE person (person_id number(4), person_nm varchar2(10));
I will also describe objects in a package called desctest, which is defined in the psdesc.tst file on the companion disk. The output I display from the DESCRIBE_PROCEDURE is generated by the psdesc (PL/SQL DESCribe) package, which is explained in the " Section 10.2.3, "DBMS_DESCRIBE Example" " section and is defined in the psdesc.spp file.
Here are various valid object specifications for DBMS_DESCRIBE.DESCRIBE_PROCEDURE:
You can also describe procedures and functions in the STANDARD and DBMS_STANDARD packages (the default packages of PL/SQL, containing the core elements of the language). To do this, you must prefix the name of the built-in with its package name, as in:
10.2.2.2 The DESCRIBE level
The level array discloses the hierarchy of the elements in a program's arguments. The level applies only to the following subset of composite datatypes: records and PL/SQL tables. The default level of 0 means that it is the top level. For scalars, that is the only level. For composites, 0 indicates that you are pointing to the actual composite argument. Each successive value of level (positive integers: 1, 2, etc.) indicates that the argument attribute or field is a child of the previous level.
The following example demonstrates how DESCRIBE_PROCEDURE generates its levels. Suppose that I have the following elements defined inside a package:
/* Filename on companion disk: psdesc.tst */* CREATE OR REPLACE PACKAGE desctest IS TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE myrec1 IS RECORD (empno NUMBER, indsal NUMBER); TYPE myrec2 IS RECORD (ename VARCHAR2(20), hiredate DATE, empno_info myrec1); TYPE myrec3 IS RECORD (deptno NUMBER, totsal NUMBER, all_emp_info myrec2); TYPE myrec_table IS TABLE OF myrec1 INDEX BY BINARY_INTEGER; PROCEDURE composites (account_in NUMBER, person person%ROWTYPE, multirec myrec3, num_table number_table, recs_table myrec_table); END; /
I have double-nested a record (myrec1 inside myrec2 inside myrec3), a table based on a record (myrec_table), and a "simple" table-based record (person%ROWTYPE). Here are the results from DBMS_DESCRIBE.DESCRIBE_PROCEDURE:
SQL> exec psdesc.showargs ('desctest.composites') OvLd Pos Lev Type Name ---- --- --- --------------- ----------------------- 0 1 0 NUMBER ACCOUNT_IN 0 2 0 RECORD PERSON 0 1 1 NUMBER PERSON_ID 0 2 1 VARCHAR2 PERSON_NM 0 3 0 RECORD MULTIREC 0 1 1 NUMBER DEPTNO 0 2 1 NUMBER TOTSAL 0 3 1 RECORD ALL_EMP_INFO 0 1 2 VARCHAR2 ENAME 0 2 2 DATE HIREDATE 0 3 2 RECORD EMPNO_INFO 0 1 3 NUMBER EMPNO 0 2 3 NUMBER INDSAL 0 4 0 INDEX-BY TABLE NUM_TABLE 0 1 1 NUMBER RETURN Value 0 5 0 INDEX-BY TABLE RECS_TABLE 0 1 1 RECORD RETURN Value 0 1 2 NUMBER EMPNO 0 2 2 NUMBER INDSAL
When you overload, you define more than one program with the same name. You will usually do this in packages. DESCRIBE_PROCEDURE creates a set of rows in the arrays for each overloading of a program. It then generates a unique, sequential number in the overload array to indicate that (a) the program is overloaded (a value of 0 indicates no overloading), and (b) to which overloading the arguments belong.
Suppose that the desctest package has two overloaded versions of the upd function (the only difference is in the datatype of the last parameter, NUMBER vs. DATE).
CREATE OR REPLACE PACKAGE desctest IS FUNCTION upd (account_in NUMBER, person person%ROWTYPE, amounts number_table, trans_date DATE) RETURN account.balance%TYPE; FUNCTION upd (account_in NUMBER, person person%ROWTYPE, amounts number_table, trans_no NUMBER) RETURN account.balance%TYPE; END; /
SQL> exec psdesc.showargs ('desctest.upd') OvLd Pos Lev Type Name ---- --- --- --------------- ------------------- 1 0 0 NUMBER RETURN Value 1 1 0 NUMBER ACCOUNT_IN 1 2 0 RECORD PERSON 1 1 1 NUMBER PERSON_ID 1 2 1 VARCHAR2 PERSON_NM 1 3 0 INDEX-BY TABLE AMOUNTS 1 1 1 NUMBER RETURN Value 1 4 0 DATE TRANS_DATE ---- --- --- --------------- ------------------- 2 0 0 NUMBER RETURN Value 2 1 0 NUMBER ACCOUNT_IN 2 2 0 RECORD PERSON 2 1 1 NUMBER PERSON_ID 2 2 1 VARCHAR2 PERSON_NM 2 3 0 INDEX-BY TABLE AMOUNTS 2 1 1 NUMBER RETURN Value 2 4 0 NUMBER TRANS_NO
The most important example I can think of for DBMS_DESCRIBE.DESCRIBE_PROCEDURE is the construction of a utility that makes it easier to use this procedure. Without such a utility, you must declare a set of PL/SQL tables every time you want to call the DESCRIBE_PROCEDURE. You must then also interpret the results. By encapsulating all of this information and these data structures inside the package, you can take advantage of DBMS_DESCRIBE.DESCRIBE_PROCEDURE much more easily, and also interpret the results with greater accuracy and understanding.
For reasons of space, I will not show the entire package specification and body. You can examine both of those in the psdesc.spp file. You will notice that I have placed all of the predefined PL/SQL tables in the package specification, even though the programs of psdesc offer a programmatic interface to those tables. I did that to make it easier to examine and manipulate the contents of the argument information.
Just to give you a sense of how psdesc does its job, here is the implementation of psdesc.args (my "substitute" for the original DESCRIBE_PROCEDURE):
/* Filename on companion disk: psdesc.spp */* PROCEDURE args (obj IN VARCHAR2) IS BEGIN g_object_name := obj; DBMS_DESCRIBE.DESCRIBE_PROCEDURE (obj, NULL, NULL, g_overload, g_position, g_level, g_argument_name, g_datatype, g_default_value, g_in_out, g_length, g_precision, g_scale, g_radix, g_spare); END;
I save the object name you specify in a package variable. I then call DESCRIBE_PROCEDURE, dumping all of the retrieved information into the predeclared PL/SQL tables.
To display all of the argument information for a program, you would call psdesc.showargs. Here is a simplified presentation of this procedure:
PROCEDURE showargs (obj IN VARCHAR2 := NULL) IS v_onearg arglist_rt; BEGIN IF obj IS NOT NULL THEN args (obj); END IF; IF g_position.COUNT > 0 THEN display_header; FOR argrow IN g_position.FIRST .. g_position.LAST LOOP v_onearg := arg (argrow); display_argument_info (v_onearg); END LOOP; END IF; END;
In other words, if the object name is specified, call psdesc.args to fill up the pre-defined arrays. Then, if there is anything in those arrays (g_position.COUNT is greater than 0), proceed from the first to the last argument and (a) call psdesc.arg to retrieve all the information for the Nth argument, and (b) display that information -- all the details of which are left to the psdesc.spp file. That was easy enough!
Here are some other aspects of psdesc you might find interesting:
Suppose that you want to perform quality assurance checks on your code (what a concept, eh?). One rule that you have established for all your developers is that no function should have OUT or IN OUT parameters. The only way that data is to be returned from a function is through the RETURN clause. This guideline improves the reusability and maintainability of the function. It also makes that function a candidate for execution within SQL.
How can you make sure that everyone is following this rule?
Sure, you could run some queries against ALL_SOURCE, which contains all the source code, but what would you look for? "IN OUT" and "OUT" are good candidates, but only when they are inside the parameter lists of functions. Hmmm. That actually involves some parsing. What's a software manager interested in code quality to do?
Let's see if DESCRIBE_PROCEDURE and the psdesc package can help. The following hasout function satisfies the request by obtaining all arguments with a call to psdesc.args and then scanning the PL/SQL table filled from DBMS_DESCRIBE.DESCRIBE_PROCEDURE for the offending parameter mode. This function returns TRUE if the program named by the string that I pass to it contains an OUT or IN OUT argument.
/* Filename on companion disk: hasout.sf */* CREATE OR REPLACE FUNCTION hasout (obj IN VARCHAR2) RETURN BOOLEAN IS v_onearg psdesc.arglist_rt; v_argrow PLS_INTEGER; retval BOOLEAN := NULL; BEGIN psdesc.args (obj); v_argrow := psdesc.numargs; IF v_argrow = 0 THEN retval := NULL; ELSE retval := FALSE; LOOP v_onearg := psdesc.arg (v_argrow); IF v_onearg.argument_name IS NOT NULL THEN retval := v_onearg.in_out IN (psdesc.c_out, psdesc.c_inout); END IF; EXIT WHEN retval OR v_argrow = 1; v_argrow := v_argrow - 1; END LOOP; END IF; RETURN retval; END; /
This function works as advertised, even for overloaded programs. Suppose, for example, that I run this function against the desctst.upd function (overloaded earlier in two versions). These functions do not contain an OUT or IN OUT parameter. I run the following script:
/* Filename on companion disk: hasout.tst */* BEGIN /* I need to call hasout with an IF statement if I am going to use DBMS_OUTPUT.PUT_LINE to show the results; that built-in is very sadly not overloaded for Booleans... */ IF hasout ('&1') THEN DBMS_OUTPUT.PUT_LINE ('&1 contains OUT or IN OUT argument(s).'); ELSE DBMS_OUTPUT.PUT_LINE ('&1 contains only IN argument(s).'); END IF; END; /
Calling this function, I get the following results:
SQL> @hasout.tst desctest.upd desctest.upd contains only IN argument(s).
If I now add an additional overloading of the desctest.upd function as follows,
I then get this result from running the hasout function:
SQL> @hasout.tst desctest.upd desctest.upd contains OUT or IN OUT argument(s).
And I bet you thought you wouldn't ever find any reason to use DESCRIBE_PROCEDURE! This handy little utility points the way to many other kinds of analyses you can perform on your code. Once you have the psdesc package in place, it becomes easy to construct these higher-level programs.
Now all you have to do is come up with a way to feed your full list of functions (both standalone and packaged) into the hasout function for its quality check. This sounds easier than it actually is. Why? Because Oracle does not offer any utilities that provide you with the list of programs defined inside of a package.
You cannot, in other words, do a describe on a package and see the list of elements defined in that package's specification. I hope that this is a shortcoming Oracle will correct, both through the provision of an API (perhaps by adding another procedure to the DBMS_DESCRIBE package) and the extension of the DESCRIBE command in SQL*Plus.
In the meantime, though, you have some options. You can get all of the stand-alone functions from ALL_OBJECTS, and that will be a start. Furthermore, if you are using PL/Vision from RevealNet (see the Preface, "About PL/Vision"), you can use the PLVcat utility to catalog your package specifications. This process will extract the names of all procedures and functions and deposit them in the plvctlg table. If that is not available, you will have to come up with a list by performing a code review on all your packages. Then put those function names (with their package names prefixed) into a database table or file. Once you have that, you can easily construct a script to read those names and pass them to hasout.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.