How DESCRIBE_PROCEDURE Works

Before proceeding to the next step in the development of OverloadCheck, I'd like to give you a sense of how DESCRIBE_PROCEDURE works, without going into all the details (I urge you to check out Oracle's documentation. How else could you possibly discover, for example, that "The total length of the name [passed to DESCRIBE_PROCEDURE] cannot exceed 197 bytes"?) Rather than provide formal instruction on this procedure, I'll give you an example and do it in a package that you can immediately put to use. You won't, in other words, have to use it directly. You see, DESCRIBE_PROCEDURE is a real pain in the neck to use. You have to declare a dozen separate collections and then load that information.

Listing 8 shows the specification of the descproc package. In lines 4 through 15, I define a record that is, in essence, a horizontal slice of all the individual collections: a single argument. The getargs procedure loads the set of collections from DBMS_DESCRIBE. That information is then available through the numargs and onearg functions. You never have to deal with the messy details, such as declaring the 12 collections, as shown in Listing 9.

With these pieces in place, I can now invoke the DESCRIBE_PROCEDURE procedure much more easily and cleanly from within getargs:

PROCEDURE getargs (obj IN VARCHAR2)
IS
BEGIN
   g_object_name := obj;


   DBMS_DESCRIBE.DESCRIBE_PROCEDURE (
      obj, 
      NULL, -- reserved 
      NULL, -- reserved
      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;

By hiding the call behind a simple interface, I can access its contents with no more code than this:

DECLARE
   l_onearg   descproc.arglist_rt;

BEGIN
   descproc.getargs ('allargs_test.upd');

   FOR indx IN 1 .. descproc.numargs
   LOOP
      l_onearg := descproc.onearg (indx);

      -- Display the data type.
      DBMS_OUTPUT.put_line (
           'Argument ' 
       || indx 

       || ' type = ' 
       || l_onearg.datatype
      );
   END LOOP;
END;
/ 

Providing chunks of reusable code lets you build quickly and robustly. Sure, this is a specialized topic, but imagine if you could choose from hundreds or thousands of chunks devoted to other specialized topics.

Forging Ahead

With a bit of knowledge about DBMS_DESCRIBE and the descproc package (which could come in handy if you ever need to use DBMS_DESCRIBE), let's move on to the central issue at hand: Do I have access to enough information to perform meaningful analysis on PL/SQL code? In other words, is it worth continuing this project?

When I look at what data is available to me, I come to two conclusions. First, ALL_ARGUMENTS and DBMS_DESCRIBE do provide a rich basis for analysis. Second, I realize that I haven't seen any utilities that attack the problem of ambiguous overloading, which means that I will be venturing into new territory—a great incentive. If I could commit some of my life to building a utility that lots of other developers can take advantage of, well, that sounds like a fine use of my time. I will get to it in the upcoming OTN articles.

Steven Feuerstein ( steven@stevenfeuerstein.com) is one of the world's leading experts on the Oracle PL/SQL language. He is the author or co-author of nine books on PL/SQL, including Oracle PL/SQL Programming, 3rd Edition and Oracle PL/SQL Best Practices (O'Reilly; http://oracle.oreilly.com/). Steven is a senior technology advisor for Quest Software, has been developing software since 1980, and worked for Oracle from 1987 to 1992. He is also past president of the board of directors of the Crossroads Fund, which makes grants to Chicago-area community groups working for social, racial, and economic justice ( www.CrossroadsFund.org).

Roadmap for Building a Code-Analysis Utility Steven Feuerstein, Oracle PS/SQL language expert, shows each step in the process of creating OverloadCheck—a utility that checks the quality of PL/SQL code. In a series of eight articles, he takes you from the beginning of the process (defining the problem he wants his utility to solve), to researching, designing, and refining this new creation until it truly works. As you watch the quality-assurance utility come to life, you'll also get exposure to some of PL/SQL's newest features and see how using best practices can benefit any coding project. Article 1: Building a Code-Analysis Utility and Doing It Right the First Time The first step shows what's involved in defining the goal of this utility, how to perform required analysis, and how to translate the results of that analysis into a useful form for developers. Article 2. Getting Started, Starting with Testing Although it would be tempting to start writing code at this stage in the process, the author shows the importance of waiting while he decides how to test his code. Article 3. Creating a High-Level Design In this article, see how to conceive a basic but workable architecture, while avoiding over designing. Article 4. Implementing OverloadCheck: The Construction Phase While showing how to get started writing code, Feuerstein demonstrates how to ensure code readability and how to minimize the number of bugs. Article 5. Adding Smarts to the Argument Information This article tackles some of the most complex logic involved in analyzing potential overloading ambiguities. It also shows how important it is to hide the complexity of such structures as multilevel and string-indexed collections behind procedures and functions. Article 6. Crafting 'Service Providers': Packages with Focused Functionality Learn by example how to identify the distinct services that relatively small units offer to higher-level packages, and how to consolidate everything related to those services. Article 7. Crafting Service Providers: Creating a Versatile Reporting Package Find out how to streamline code with the dynamic WHERE clause and how to put a user-friendly wrapper around unwieldy-but-useful procedures (such as DBMS_UTILITY.NAME_RESOLVE and DBMS_OUTPUT.PUT_LINE) as you explore the creation of OverloadCheck's reporting package. Article 8. Getting it Right the Second Time In the final stage, Feuerstein uses a test engine and his predefined test cases to do some testing. He also shows how to use the process of refactoring to fix problems and improve the inner workings of the program.

Table 1: Descriptions of ALL_ARGUMENTS columns, from Oracle documentation
Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Name of the owner of the object
OBJECT_NAME	VARCHAR2(30)		Name of the procedure or function
PACKAGE_NAME	VARCHAR2(30)		Package name
OBJECT_ID	NUMBER	NOT NULL	Name of the package containing the procedure or function
OVERLOAD	VARCHAR2(40)		Overload unique identifier
ARGUMENT_NAME	VARCHAR2(30)		Name of the argument
POSITION	NUMBER	NOT NULL	Position in argument list, or NULL for function return value
SEQUENCE	NUMBER	NOT NULL	Argument sequence, including all nesting levels
DATA_LEVEL	NUMBER	NOT NULL	Nesting depth of argument for composite types
DATA_TYPE	VARCHAR2(14)		Datatype of the argument
DEFAULT_VALUE	LONG		Default value for the argument
DEFAULT_LENGTH	NUMBER		Length of default value for the argument
IN_OUT	VARCHAR2(9)		Argument direction (IN, OUT, or IN/OUT)
DATA_LENGTH	NUMBER		Length of the column in bytes
DATA_PRECISION	NUMBER		Length in decimal digits (NUMBER) or binary digits (FLOAT)
DATA_SCALE	NUMBER		Digits to right of decimal point in a number
RADIX	NUMBER		Argument radix for a number
CHARACTER_SET_NAME	VARCHAR2(44)		Character set name for the argument
TYPE_OWNER	VARCHAR2(30)		User name of the owner of the type of the argument
TYPE_NAME	VARCHAR2(30)		Name of the type of the argument. If the type is a package local type (that is, it is declared in a package specification), this column displays the name of the package.
TYPE_SUBNAME	VARCHAR2(30)		Relevant only for package local types. Displays the name of the type declared in the package identified in the TYPE_NAME column.
TYPE_LINK	VARCHAR2(128)		Relevant only for package local types when the package identified in the TYPE_NAME column is a remote package. This column displays the database link used to refer to the remote package.
PLS_TYPE	VARCHAR2(30)		For numeric arguments, the name of the PL/SQL type of the argument. NULL otherwise.