You should always maintain the source code of your programs in text files (or via a development tool specifically designed to store and manage PL/SQL code outside of the database). When you store these programs in the database, however, you can take advantage of SQL to analyze your source code across all modules, which may not be a straightforward task with your text editor.

The USER_SOURCE view contains all of the source code for objects owned by the current user. The structure of USER_SOURCE is as follows:

  Name              Null?     Typ e
  ----------------- --------- ----
  NAME              NOT NULL  VARCHAR2(30)
  TYPE                        VARCHAR2(12) 
  LINE              NOT NULL  NUMBER
  TEXT                        VARCHAR2(4000)

where:

NAME
   Is the name of the object

TYPE
   Is the type of the object (ranging from PL/SQL 
   program units to Java source to trigger source)

LINE
   Is the line number

TEXT
   Is the text of the source code

USER_SOURCE
   is a very valuable resource for developers. With the 
   right kind of queries, you can do things like:

1. Display source code for a given line number
   
2. Validate coding standards
   
3. Identify possible bugs or weaknesses in your source code
   
4. Look for programming constructs not identifiable from other views

Suppose, for example, that we have set as a rule that individual developers should never hardcode one of those application-specific error numbers between
–20,999 and –20,000 (such hardcodings can lead to conflicting usages and lots of confusion). I can’t stop a developer from writing code like this:

  RAISE_APPLICATION_ERROR (-20306, 'Balance too low');

but I can create a package that allows me to identify all the programs that have such a line in them. I call it my “validate standards” package; it is very simple, and its main procedure looks like this:

  /* Files on web: valstd.pks, valstd.pkb */
  PROCEDURE progwith (str IN VARCHAR2)
  IS
    TYPE info_rt IS RECORD (
      NAME  user_source.NAME%TYPE
    , text  user_source.text%TYPE
    );

    TYPE info_aat IS TABLE OF info_rt
       INDEX BY PLS_INTEGER;

    info_aa info_aat;
BEGIN
    SELECT NAME || '-' || line
         , text
    BULK COLLECT INTO info_aa
      FROM user_source
     WHERE UPPER (text) LIKE '%' || UPPER (str) || '%'
       AND NAME <> 'VALSTD'
       AND NAME <> 'ERRNUMS';

    disp_header ('Checking for presence of "' || str || '"');

    FOR indx IN info_aa.FIRST .. info_aa.LAST
    LOOP
       pl (info_aa (indx).NAME, info_aa (indx).text);
    END LOOP;
  END progwith;

Once this package is compiled into my schema, I can check for usages of –20,NNN numbers with this command:

  SQL> EXEC valstd.progwith ('-20') 
  ==================
  VALIDATE STANDARDS
  ====================
  Checking for presence of "-20"

  CHECK_BALANCE - RAISE_APPLICATION_ERROR
(-20306, 'Balance too low');

  MY_SESSION -   PRAGMA EXCEPTION_INIT(dblink_not_open,-2081);
  VSESSTAT - CREATE DATE   : 1999-07-20

Notice that the second and third lines in my output are not really a problem; they show up only because I couldn’t define my filter narrowly enough.

This is a fairly crude analytical tool, but you could certainly make it more sophisti cated. You could also have it generate HTML that is then posted on your intranet. You could then run the valstd scripts every Sunday night through a DBMS_JOB-submitted job, and each Monday morning developers could check the intranet for feedback on any fixes needed in their code.

Please check back next week for the continuation of this article.