10.2.1.1 DBMS_DESCRIBE program

Table 10.2 summarizes the single procedure available through DBMS_DESCRIBE.

10.2.1.2 DBMS_DESCRIBE nonprogram elements

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.

The values for parameter datatypes are listed in the following table.

10.2.1.4 Exceptions

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.

10.2.1.5 Restrictions

There are several limitations on using DESCRIBE_PROCEDURE:

• You cannot describe remote objects (i.e., PL/SQL program elements that are defined in another database instance).

• You cannot get a describe or a listing of all elements defined in a package specification. You need to know the name of the procedure or function within the package in other to get a describe of it.

• DBMS_DESCRIBE.DESCRIBE_PROCEDURE will not show you the internal structure (attributes) of Oracle8 elements such as object types, variable arrays, and nested tables.

10.2.2.1 Specifying a program name

The valid syntax for a PL/SQL object to be described follows:

[[part1.]part2.]part3

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:

'STANDARD.TO_CHAR'

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

10.2.2.3 How overloading is handled

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;
/

Then the output from DBMS_DESCRIBE.DESCRIBE_PROCEDURE would be as follows:

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

10.2.3.1 Features of the psdesc package

The psdesc package offers those features (PL/SQL Release 2.3 or later is needed to compile and use this package). Found in the psdesc.spp file, it contains the following elements:

• A set of constants that give names to each of the different datatype values. These constants allow you to write code without having to remember specific hard-coded values. Here are a few lines from that code:

     c_varchar2       CONSTANT PLS_INTEGER := 1;
     c_number         CONSTANT PLS_INTEGER := 2;
     c_object_type    CONSTANT PLS_INTEGER := 121;

• A PL/SQL table containing names to go along with those datatype constants (numbers). The psdesc.showargs program uses this table to display more descriptive information about the argument (for example, more than simply saying that it is type 121).

• A set of constants that give names to the values for the different parameter modes. These are defined as follows:

     c_in CONSTANT PLS_INTEGER := 0;
     c_out CONSTANT PLS_INTEGER := 1;
     c_inout CONSTANT PLS_INTEGER := 2;

• A user-defined record type that parallels the set of PL/SQL tables populated by the DESCRIBE_PROCEDURE procedure. This record type is defined as follows:

     TYPE arglist_rt IS RECORD (
       overload NUMBER,
       position NUMBER
      ,level NUMBER
      ,argument_name VARCHAR2 (30)
      ,datatype NUMBER
      ,default_value NUMBER
      ,in_out NUMBER
      ,length NUMBER
      ,precision NUMBER
      ,scale NUMBER
      ,radix NUMBER);

  This record type is the RETURN value for the psdesc.arg function.

• A procedure that acts as a wrapper around the DESCRIBE_PROCEDURE procedure. The psdesc.args procedure has a much simpler interface.

     PROCEDURE psdesc.args (obj IN VARCHAR2);

  When you call it, you don't need to provide a set of predeclared PL/SQL tables. Those arrays are already defined in the psdesc package specification.

• A procedure that displays all of the argument information in a very readable format. You have seen the output (or part of it) in a number of earlier sections in this chapter.

     PROCEDURE psdesc.showargs (obj IN VARCHAR2 := NULL);

  Notice that this procedure has an optional object name; if you don't provide one, it will show you the arguments for whatever program was last processed in a call to psdesc.args. In other words, it will examine whatever is sitting in the individual arrays.

• A procedure that returns information about a specified argument (by position in the arrays).

     FUNCTION psdesc.arg (pos IN INTEGER) RETURN arglist_rt;

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:

• Use of the package initialization section to fill g_datatype_names and g_mode_names, which are lists of "translations" for the numeric codes.

• The use of a local function, strval, defined inside psdesc.showargs, which consolidates otherwise redundant logic used to format output for display.

• The check for a non-NULL g_object_name in the psdesc.arg function to make sure that you have used psdesc.args or psdesc.showargs to fill up the predefined PL/SQL tables. This is a sure-fire validation step, since the g_object_name variable is defined in the package body. It is private data and is only modified by a call to psdesc.arg.

10.2.3.2 Using psdesc.args as a quality assurance tool

Rather than spend any more space on the implementation of psdesc, I will show you how you might put it to use.

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,

CREATE OR REPLACE PACKAGE desctest
IS
   FUNCTION upd (account_in NUMBER, 
     person person%ROWTYPE, 
     amounts number_table,
     trans_no NUMBER,
     maxsal OUT NUMBER) RETURN account.balance%TYPE;
END;
/

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.