The USER_OBJECTS view contains the following key information about an object:

The following SQL*Plus script displays the status of PL/SQL code objects:

/* File on web: psobj.sql */
SET PAGESIZE 66
COLUMN object_type FORMAT A20
COLUMN object_name FORMAT A30
COLUMN status FORMAT A10
BREAK ON object_type SKIP 1
SPOOL psobj.lis
SELECT object_type, object_name, status
  FROM user_objects
 WHERE object_type IN (
    'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE',
    'TYPE', 'TYPE BODY', 'TRIGGER')
 ORDER BY object_type, status, object_name
/
SPOOL OFF

The output from this script file contains the following list:

OBJECT_TYPE          OBJECT_NAME                    STATUS
-------------------- ------------------------------ ----------
FUNCTION             DEVELOP_ANALYSIS               INVALID
                     NUMBER_OF_ATOMICS              INVALID
                                                   
PACKAGE              CONFIG_PKG                     VALID
                     EXCHDLR_PKG                    VALID

Notice that a two of my modules are marked as INVALID. See the section "Recompiling Invalid Code" for more details on the significance of this setting and how you can change it to VALID.

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?    Type
 ------------------------------- -------- ----
 NAME                            NOT NULL VARCHAR2(30)
 TYPE                                     VARCHAR2(12)
 LINE                            NOT NULL NUMBER
 TEXT                                     VARCHAR2(4000)

where:

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

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 sophisticated. 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.

The USER_OBJECT_SIZE view gives you the following information about the size of the programs stored in the database:

Here is a query that allows you to show code objects that are larger than a given size. You might want to run this query to identify the programs that you will want to pin into the database using DBMS_SHARED_POOL (see Chapter 23 for more information on this package) in order to minimize the swapping of code in the SGA:

/* File on web: pssize.sql */
SELECT name, type, source_size, parsed_size, code_size
  FROM user_object_size
 WHERE code_size > &&1 * 1024
 ORDER BY code_size DESC
/

The USER_PLSQL_OBJECT_SETTINGS (introduced in Oracle Database 10g Release 1) view provides information about the following compiler settings of a stored PL/SQL object:

Possible uses for this view include:

The USER_PROCEDURES view lists all functions and procedures, along with associated properties, including whether a function is pipelined, parallel enabled, or aggregate. USER_PROCEDURES will also show you the AUTHID setting for a program (DEFINER or CURRENT_USER). This can be very helpful if you need to see quickly which programs in a package or group of packages use invoker rights or definer rights. Here is an example of such a query:

/* File on web: show_authid.sql */
SELECT   AUTHID
       , p.object_name program_name
       , procedure_name subprogram_name
    FROM user_procedures p, user_objects o
   WHERE p.object_name = o.object_name
     AND p.object_name LIKE '<package or program name criteria>'
ORDER BY AUTHID, procedure_name;

Query the trigger-related views (USER_TRIGGERS, USER_TRIG_COLUMNS) to do any of the following:

Here is a query you can use to identify potentially problematic triggers lacking a WHEN clause:

/* File on web: nowhen_trigger.sql */
SELECT *
  FROM user_triggers tr
 WHERE when_clause IS NULL AND
       EXISTS (SELECT 'x'
                 FROM user_trigger_cols
                WHERE trigger_owner = USER
                AND trigger_name = tr.trigger_name);

A very useful view for programmers is USER_ARGUMENTS. It contains information about each of the arguments of each of the stored programs in your schema. It offers, simultaneously, a wealth of nicely parsed information about arguments and a bewildering structure that is very hard to work with.

Here is a simple SQL*Plus script to dump the contents of USER_ARGUMENTS for all the programs in the specified package:

/* File on web: desctest.sql */
SELECT object_name, argument_name, overload
     , POSITION, SEQUENCE, data_level, data_type
  FROM user_arguments
 WHERE package_name = UPPER ('&&1'),

A more elaborate PL/SQL-based program for displaying the contents of USER_ARGUMENTS may be found in the show_all_arguments.sp file on the book’s web site.

You can also write more specific queries against USER_ARGUMENTS to identify possible quality issues with your code base. For example, Oracle recommends that you stay away from the LONG datatype and instead use LOBs. In addition, the fixed-length CHAR datatype can cause logic problems; you are much better off sticking with VARCHAR2. Here is a query that uncovers the usage of these types in argument definitions:

/* File on web: long_or_char.sql */
SELECT object_name, argument_name, overload
     , POSITION, SEQUENCE, data_level, data_type
  FROM user_arguments
 WHERE data_type IN ('LONG','CHAR'),

You can even use USER_ARGUMENTS to deduce information about a package’s program units that is otherwise not easily obtainable. Suppose that I want to get a list of all the procedures and functions defined in a package specification. You will say: “No problem! Just query the USER_PROCEDURES view.” And that would be a fine answer, except that it turns out that USER_PROCEDURES doesn’t tell you whether a program is a function or procedure (in fact, it can be both, depending on how the program is overloaded!).

You might instead, want to turn to USER_ARGUMENTS. It does, indeed, contain that information, but it is far less than obvious. To determine whether a program is a function or a procedure, you must check to see if there is a row in USER_ARGUMENTS for that package-program combination that has a POSITION of 0. That is the value Oracle uses to store the RETURN “argument” of a function. If it is not present, then the program must be a procedure.

The following function uses this logic to return a string that indicates the program type (if it is overloaded with both types, the function returns “FUNCTION, PROCEDURE”). Note that the list_to_string function used in the main body is provided in the file.

/* File on web: program_type.sf */
CREATE OR REPLACE FUNCTION program_type (
   owner_in     IN   VARCHAR2
 , package_in   IN   VARCHAR2
 , program_in   IN   VARCHAR2
)
   RETURN VARCHAR2
IS
   TYPE overload_aat IS TABLE OF all_arguments.overload%TYPE
      INDEX BY PLS_INTEGER;
 
   l_overloads  overload_aat;
   retval       VARCHAR2 (32767);
 
BEGIN
   SELECT   DECODE (MIN (POSITION), 0, 'FUNCTION', 'PROCEDURE')
   BULK COLLECT INTO l_overloads
       FROM all_arguments
      WHERE owner = owner_in
        AND package_name = package_in
        AND object_name = program_in
   GROUP BY overload;
 
   IF l_overloads.COUNT > 0
   THEN
      retval := list_to_string (l_overloads, ',', distinct_in => TRUE);
   END IF;
 
   RETURN retval;
END program_type;
/

Finally, you should also know that the built-in package, DBMS_DESCRIBE, provides a PL/SQL API to provide much of the same information as USER_ARGUMENTS. There are differences, however, in the way these two elements handle datatypes.

You can use the ALTER command to recompile a single program. Here are examples of using this DDL command:

ALTER FUNCTION a_function COMPILE REUSE SETTINGS;
ALTER PACKAGE my_package COMPILE REUSE SETTINGS;
ALTER PACKAGE my_package COMPILE SPECIFICATION REUSE SETTINGS;
ALTER PACKAGE my_package COMPILE BODY REUSE SETTINGS;

You should include the REUSE SETTINGS clause so that other settings for this program (such as compile-time warnings and optimization level) are not inadvertently set to the settings of the current session.

Of course, if you have many invalid objects, you will not want to type ALTER COMPILE commands for each one. You could write a simple query, like the one below, to generate all the ALTER commands:

SELECT 'ALTER ' || object_type || ' ' || object_name
       || ' COMPILE REUSE SETTINGS;'
  FROM user_objects
 WHERE status = 'INVALID';

The problem with this “bulk” approach is that as you recompile one invalid object, you may cause many others to be marked INVALID. You are much better off relying on Oracle’s own utilities to recompile entire schemas or to use a sophisticated, third-party script created by Solomon Yakobson. These are described in the next section.

Starting with Oracle Database 10g Release 1, the UTL_RECOMP built-in package offers two programs that you can use to recompile any invalid objects in your schema: RECOMP_SERIAL and RECOMP_PARALLEL.

To use UTL_RECOMP, you will need to connect as a SYSDBA account. When running the parallel version, it uses the DBMS_JOB package to queue up the recompile jobs. When this happens, all other jobs in the queue are temporarily disabled to avoid conflicts with the recompilation.

Here is an example of calling the serial version to recompile all invalid objects in the SCOTT schema:

SQL>CALL utl_recomp.recomp_serial ('SCOTT'),

If you have multiple processors, the parallel version may help you complete your recompilations more rapidly. As Oracle notes in its documentation of this package, however, compilation of stored programs results in updates to many catalog structures and is I/O intensive; the resulting speedup is likely to be a function of the speed of your disks.

Here is an example of requesting recompilation of all invalid objects in the SCOTT schema, using up to four simultaneous threads for the recompilation steps:

SQL>CALL utl_recomp.recomp_parallel ('SCOTT', 4);

If you do not want to have to connect to a SYSDBA account to perform your recompilations, you might consider using a recompile utility written by Solomon Yakobson and found in the recompile.sql file on the book’s web site.

A very useful compile-time warning is PLW-06002: Unreachable code. Consider the following program (available in the cantgothere.sql file on the book’s web site). Because I have initialized the salary variable to 10,000, the conditional statement will always send me to line 9. Line 7 will never be executed.

  /* File on web: cantgothere.sql */
1    CREATE OR REPLACE PROCEDURE cant_go_there
2    AS
3       l_salary NUMBER := 10000;
4    BEGIN
5       IF l_salary > 20000
6       THEN
7          DBMS_OUTPUT.put_line ('Executive'),
8       ELSE
9          DBMS_OUTPUT.put_line ('Rest of Us'),
10       END IF;
11    * END cant_go_there;

If I compile this code in any release prior to Oracle Database 10g Release 1, I am simply told “Procedure created.” If, however, I have enabled compile-time warnings in my session on the new release and then try to compile the procedure, I get this response from the compiler:

SP2-0804: Procedure created with compilation warnings
 
SQL>sho err
Errors for PROCEDURE CANT_GO_THERE:
 
LINE/COL ERROR
-------- --------------------------------------
7/7      PLW-06002: Unreachable code

Given this warning, I can now go back to that line of code, determine why it is unreachable, and make the appropriate corrections.

If you are running 10.1.0.2.0 on Windows, and try to reproduce what I showed in the previous section, you will see this message:

7/7      PLW-06002: Message 6002 not found;
            No message file for product=plsql, facility=PLW

The problem is that Oracle didn’t ship the message file, plwus.msb, with the Oracle Database 10g software until 10.1.0.3.0, and the download available on OTN is 10.1.0.2.0. If you encounter this problem, you will need to contact Oracle Support to obtain this file (reference Bug 3680132) and place it in the plsqlmesg subdirectory. You will then be able to see the actual warning message.

If you are running a pre-Oracle Database 10g version of SQL*Plus, it will not be able to display warnings; because Oracle9i Database did not support compile-time warnings, commands like SHOW ERRORS don’t even try to obtain warning information.

Specifically, the ALL_ERRORS family of data dictionary views has two new columns in Oracle Database 10g: ATTRIBUTE and MESSAGE_NUMBER. The earlier SQL*Plus versions don’t know how to interpret these columns.

To determine if you are using a pre-Oracle Database 10g version of SQL*Plus, execute these commands in SQL*Plus:

CREATE TABLE t (n BINARY_FLOAT)
/
DESCRIBE t

In such versions of SQL*Plus, you will see that “n” is characterized as “UNDEFINED.” Starting with Oracle Database 10g Release 1, SQL*Plus will properly show the type of this column to be “BINARY_FLOAT.”

Oracle allows you to turn compile-time warnings on and off, and also to specify the type of warnings that interest you. There are three categories of warnings:

Oracle lets you enable/disable compile-time warnings for a specific category, for all categories, and even for specific, individual warnings. You can do this with either the ALTER DDL command or the DBMS_WARNING built-in package.

To turn on compile-time warnings in your system as a whole, issue this command:

ALTER SYSTEM SET PLSQL_WARNINGS='string'

The following command, for example, turns on compile-time warnings in your system for all categories:

ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL';

This is a useful setting to have in place during development because it will catch the largest number of potential issues in your code.

To turn on compile-time warnings in your session for severe problems only, issue this command:

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE';

And if you want to alter compile-time warnings settings for a particular, already-compiled program, you can issue a command like this:

ALTER PROCEDURE hello COMPILE PLSQL_WARNINGS='ENABLE:ALL' REUSE SETTINGS;

You can tweak your settings with a very high level of granularity by combining different options. For example, suppose that I want to see all performance-related issues, that I will not concern myself with server issues for the moment, and that I would like the compiler to treat PLW-05005: function exited without a RETURN as a compile error. I would then issue this command:

ALTER SESSION SET PLSQL_WARNINGS=
  'DISABLE:SEVERE'
 ,'ENABLE:PERFORMANCE'
 ,'ERROR:05005';

I especially like this “treat as error” option. Consider the PLW-05005: function returns without value warning. If I leave PLW-05005 simply as a warning, then when I compile my no_return function, shown below, the program does compile, and I can use it in my application.

SQL>CREATE OR REPLACE FUNCTION no_return
  2     RETURN VARCHAR2
  3  AS
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE (
  6        'Here I am, here I stay'),
  7  END no_return;
  8  /
 
SP2-0806: Function created with compilation warnings
 
SQL> sho err
Errors for FUNCTION NO_RETURN:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05005: function NO_RETURN returns without value at line 7

If I now alter the treatment of that error with the ALTER SESSION command shown above and then recompile no_return, the compiler stops me in my tracks:

Warning: Procedure altered with compilation errors

By the way, I could also change the settings for that particular program only, to flag this warning as a “hard” error with a command like this:

ALTER PROCEDURE no_return COMPILE PLSQL_WARNINGS = 'error:6002' REUSE SETTINGS
/

You can, in each of these variations of the ALTER command, also specify ALL as a quick and easy way to refer to all compile-time warnings categories, as in:

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Oracle also provides the DBMS_WARNING package, which provides the same capabilities to set and change compile-time warning settings through a PL/SQL API. DBMS_WARNING also goes beyond the ALTER command, allowing you to make changes to those warning controls that you care about while leaving all the others intact. You can also easily restore the original settings when you’re done.

DBMS_WARNING was designed to be used in install scripts in which you might need to disable a certain warning, or treat a warning as an error, for individual program units being compiled. You might not have any control over the scripts surrounding those for which you are responsible. Each script’s author should be able to set the warning settings he wants, while inheriting a broader set of settings from a more global scope.

The NOCOPY compiler hint tells Oracle that, if possible, you would like it to not make a copy of your IN OUT arguments. This can improve the performance of programs that pass large data structures, such as collections or CLOBs.

You need to include the NOCOPY hint in both the specification and the body of your program (relevant for packages and object types). If the hint is not present in both, Oracle will apply whatever is specified in the specification.

Here is an example of code that will generate this warning:

/* File on web: plw5000.sql */
CREATE OR REPLACE PACKAGE plw5000
IS
   TYPE collection_t IS
      TABLE OF VARCHAR2 (100);
 
   PROCEDURE proc (
      collection_in IN OUT NOCOPY
         collection_t);
END plw5000;
/
CREATE OR REPLACE PACKAGE BODY plw5000
IS
   PROCEDURE proc (
      collection_in IN OUT
         collection_t)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE ('Hello!'),
   END proc;
END plw5000;
/

Compile-time warnings will display as follows:

SQL>SHOW ERRORS PACKAGE BODY plw5000
Errors for PACKAGE BODY PLW5000:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/20     PLW-05000: mismatch in NOCOPY qualification between specification
         and body
 
3/20     PLW-07203: parameter 'COLLECTION_IN' may benefit from use of the
         NOCOPY compiler hint

This warning will make itself heard when you have declared more than one variable or constant with the same name. It can also pop up if the parameter list of a program defined in a package specification is different from that of the definition in the package body.

You may be saying to yourself: I’ve seen that error before, but it is a compilation error, not a warning. And, in fact, you are right, in that the following program simply will not compile:

CREATE OR REPLACE PROCEDURE plw5001
IS
   a   BOOLEAN;
   a   PLS_INTEGER;
BEGIN
   a := 1;
   DBMS_OUTPUT.put_line ('Will not compile'),
END plw5001;
/

You receive the following compile error: PLS-00371: at most one declaration for ‘A’ is permitted in the declaration section.

So why is there a warning for this situation? Consider what happens when I remove the assignment to the variable named a:

SQL>CREATE OR REPLACE PROCEDURE plw5001
  2  IS
  3     a   BOOLEAN;
  4     a   PLS_INTEGER;
  5  BEGIN
  6     DBMS_OUTPUT.put_line ('Will not compile?'),
  7  END plw5001;
  8  /
Procedure created.

The program compiles! Oracle does not flag the PLS-00371 because I have not actually used either of the variables in my code. The PLW-05001 warning fills that gap by giving us a heads-up if we have declared, but not yet used, variables with the same name, as you can see here:

SQL>ALTER PROCEDURE plw5001 COMPILE plsql_warnings = 'enable:all';
SP2-0805: Procedure altered with compilation warnings
SQL> SHOW ERRORS
Errors for PROCEDURE PLW5001:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/4      PLW-05001: previous use of 'A' (at line 3) conflicts with this use

When you use NOCOPY with an IN OUT parameter, you are asking PL/SQL to pass the argument by reference, rather than by value. This means that any changes to the argument are made immediately to the variable in the outer scope. “By value” behavior (NOCOPY is not specified or the compiler ignores the NOCOPY hint), on the other hand, dictates that changes within the program are made to a local copy of the IN OUT parameter. When the program terminates, these changes are then copied to the actual parameter. (If an error occurs, the changed values are not copied back to the actual parameter.)

Use of the NOCOPY hint increases the possibility that you will run into the issue of argument aliasing, in which two different names point to the same memory location. Aliasing can be difficult to understand and debug; a compile-time warning that catches this situation will come in very handy.

Consider this program:

/* File on web: plw5003.sql */
CREATE OR REPLACE PROCEDURE very_confusing (
   arg1   IN              VARCHAR2
 , arg2   IN OUT          VARCHAR2
 , arg3   IN OUT NOCOPY   VARCHAR2
)
IS
BEGIN
   arg2 := 'Second value';
   DBMS_OUTPUT.put_line ('arg2 assigned, arg1 = ' || arg1);
   arg3 := 'Third value';
   DBMS_OUTPUT.put_line ('arg3 assigned, arg1 = ' || arg1);
END;
/

It’s a simple enough program. pass in three strings, two of which are IN OUT; assign values to those IN OUT arguments; and display the value of the first IN argument’s value after each assignment.

Now I will run this procedure, passing the very same local variable as the argument for each of the three parameters:

SQL>DECLARE
  2     str   VARCHAR2 (100) := 'First value';
  3  BEGIN
  4     DBMS_OUTPUT.put_line ('str before = ' || str);
  5     very_confusing (str, str, str);
  6     DBMS_OUTPUT.put_line ('str after = ' || str);
  7  END;
  8  /
str before = First value
arg2 assigned, arg1 = First value
arg3 assigned, arg1 = Third value
str after = Second value

Notice that while still running very_confusing, the value of the arg1 argument was not affected by the assignment to arg2. Yet when I assigned a value to arg3, the value of arg1 (an IN argument) was changed to “Third value”! Furthermore, when very_confusing terminated, the assignment to arg2 was applied to the str variable. Thus, when control returned to the outer block, the value of the str variable was set to “Second value”, effectively writing over the assignment of “Third value”.

As I said earlier, parameter aliasing can be very confusing. So, if you enable compile-time warnings, programs such as plw5003 may be revealed to have potential aliasing problems:

SQL>CREATE OR REPLACE PROCEDURE plw5003
  2  IS
  3     str   VARCHAR2 (100) := 'First value';
  4  BEGIN
  5     DBMS_OUTPUT.put_line ('str before = ' || str);
  6     very_confusing (str, str, str);
  7     DBMS_OUTPUT.put_line ('str after = ' || str);
  8  END plw5003;
  9  /
 
SP2-0804: Procedure created with compilation warnings
 
SQL> sho err
Errors for PROCEDURE PLW5003:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/4      PLW-05003: same actual parameter(STR and STR) at IN and NOCOPY
         may have side effects
 
6/4      PLW-05003: same actual parameter(STR and STR) at IN and NOCOPY
         may have side effects

Many PL/SQL developers are unaware of the STANDARD package, and its implications for their PL/SQL code. For example, it is common to find programmers who assume that names like INTEGER and TO_CHAR are reserved words in the PL/SQL language. That is not the case. They are, respectively, a datatype and a function declared in the STANDARD package.

STANDARD is one of the two default packages of PL/SQL (the other is DBMS_STANDARD). Because STANDARD is a default package, you do not need to qualify references to datatypes like INTEGER, NUMBER, PLS_INTEGER, etc., with “STANDARD"--but you could, if you so desired.

PLW-5004 notifies you if you happen to have declared an identifier with the same name as an element in STANDARD (or a SQL built-in; most built-ins—but not all—are declared in STANDARD).

Consider this procedure definition:

1    CREATE OR REPLACE PROCEDURE plw5004
2    IS
3       INTEGER   NUMBER;
4     
5       PROCEDURE TO_CHAR
6       IS
7       BEGIN
8          INTEGER := 10;
9       END TO_CHAR;
10    BEGIN
11       TO_CHAR;
12    * END plw5004;

Compile-time warnings for this procedure will display as follows:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/4      PLW-05004: identifier INTEGER is also declared in STANDARD
         or is a SQL builtin
5/14     PLW-05004: identifier TO_CHAR is also declared in STANDARD
         or is a SQL builtin

You should avoid reusing the names of elements defined in the STANDARD package unless you have a very specific reason to do so.

This warning makes me happy. A function that does not return a value is a very badly designed program. This is a warning that I would recommend you ask Oracle to treat as an error with the “ERROR:5005” syntax in your PLSQL_WARNINGS setting.

You already saw one example of such a function—no_return. That was a very obvious chunk of code; there wasn’t a single RETURN in the entire executable section. Your code will, of course, be more complex. The fact that a RETURN may not be executed could well be hidden within the folds of complex conditional logic.

At least in some of these situations, though, Oracle will still detect the problem. The following program demonstrates one of those situations:

SQL>CREATE OR REPLACE FUNCTION no_return (
  2     check_in IN BOOLEAN)
  3     RETURN VARCHAR2
  4  AS
  5  BEGIN
  6     IF check_in
  7     THEN
  8        RETURN 'abc';
  9     ELSE
 10        DBMS_OUTPUT.put_line (
 11        'Here I am, here I stay'),
 12     END IF;
 13  END no_return;
 14  /
 
SP2-0806: Function created with compilation warnings
 
SQL> SHOW ERRORS
Errors for FUNCTION NO_RETURN:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05005: function NO_RETURN returns without value at line 13

Oracle has detected a branch of logic that will not result in the execution of a RETURN, so it flags the program with a warning. The plw5005.sql file on the book’s web site contains even more complex conditional logic, demonstrating that the warning is raised for less trivial code structures as well.

Oracle will now perform static (compile-time) analysis of your program to determine if any lines of code in your program will never be reached during execution. This is extremely valuable feedback to receive, but you may find that the compiler warns you of this problem on lines that do not, at first glance, seem to be unreachable. In fact, Oracle notes in the description of the action to take for this error that you should “disable the warning if much code is made unreachable intentionally and the warning message is more annoying than helpful.” I will come back to this issue at the end of the section.

You already saw an example of this compile-time warning in the “A quick example” section at the beginning of this section. Now consider the following code:

/* File on web: plw6002.sql */
1    CREATE OR REPLACE PROCEDURE plw6002
2    AS
3       l_checking BOOLEAN := FALSE;
4    BEGIN
5       IF l_checking
6       THEN
7          DBMS_OUTPUT.put_line ('Never here...'),
8       ELSE
9          DBMS_OUTPUT.put_line ('Always here...'),
10          GOTO end_of_function;
11       END IF;
12       <<end_of_function>>
13       NULL;
14    14* END plw6002;

Oracle shows the following compile-time warnings for this program:

LINE/COL ERROR
-------- ------------------------------
5/7      PLW-06002: Unreachable code
7/7      PLW-06002: Unreachable code
13/4     PLW-06002: Unreachable code

I see why line 7 is marked as unreachable: l_checking is set to FALSE, and so line 7 can never run. But why is line 5 marked “unreachable.” It seems as though, in fact, that code would always be run! Furthermore, line 13 will always be run as well because the GOTO will direct the flow of execution to that line through the label. Yet it is tagged as unreachable.

The reason for this behavior is simple: the unreachable code warning is generated after optimization of the code. To determine unreachability, the compiler has to translate the source code into an internal representation so that it can perform the necessary analysis of the control flow.

The compiler does not give you false positives; when it says that line N is unreachable, it is telling you that the line truly will never be executed, accurately reflecting the optimized code.

There are currently scenarios of unreachable code that are not flagged by the compiler. Here is one example:

/* File on web: plw6002.sql */
CREATE OR REPLACE FUNCTION plw6002 RETURN VARCHAR2
AS
BEGIN
   RETURN NULL;
   DBMS_OUTPUT.put_line ('Never here...'),
END plw6002;
/

Certainly, the call to DBMS_OUTPUT.PUT_LINE is unreachable, but the compiler does not currently detect that state. This scenario, and others like it, may be covered in future releases of the compiler.

As mentioned earlier in relation to PLW-05005, use of NOCOPY with complex, large IN OUT parameters can improve the performance of programs under certain conditions. This warning will flag programs whose IN OUT parameters might benefit from NOCOPY. Here is an example of such a program:

/* File on web: plw7203.sql */
CREATE OR REPLACE PACKAGE plw7203
IS
   TYPE collection_t IS TABLE OF VARCHAR2 (100);
 
   PROCEDURE proc (collection_in IN OUT collection_t);
END plw7203;
/

This is another one of those warnings that will be generated for lots of programs and may become a nuisance. The warning/recommendation is certainly valid, but for most programs the impact of this optimization will not be noticeable. Furthermore, you are unlikely to switch to NOCOPY without making other changes in your code to handle situations where the program terminates before completing, possibly leaving your data in an uncertain state.

This warning will surface when you call a SQL statement from within PL/SQL and rely on implicit conversions within that statement. Here is an example:

/* File on web: plw7204.sql */
CREATE OR REPLACE FUNCTION plw7204
   RETURN PLS_INTEGER
AS
   l_count PLS_INTEGER;
BEGIN
   SELECT COUNT(*) INTO l_count
     FROM employee
    WHERE salary = '10000';
   RETURN l_count;
END plw7204;
/

The salary column is numeric, but I am comparing it to a string value. The optimizer may well disable the use of an index on salary because of this implicit conversion.

Related tightly to this warning is PLW-7202: bind type would result in conversion away from column type.

The $IF directive can reference constants defined in your own packages. In the example below, I vary the way that the bonus is applied depending on whether or not the location in which this third-party application is installed is complying with the Sarbanes-Oxley guidelines. Such a setting is unlikely to change for a long period of time. If I rely on the traditional conditional statement in this case, I will leave in place a branch of logic that should never be applied. With conditional compilation, the code is removed before compilation.

/* File on web: cc_my_package.sql */
CREATE OR REPLACE PROCEDURE apply_bonus (
   id_in IN employee.employee_id%TYPE
  ,bonus_in IN employee.bonus%TYPE)
IS
BEGIN
   UPDATE employee
      SET bonus =
       $IF employee_rp.apply_sarbanes_oxley
       $THEN
          LEAST (bonus_in, 10000)
       $ELSE
          bonus_in
       $END
     WHERE employee_id = id_in;
   NULL;
END apply_bonus;
/

We can now set up our own debug/trace mechanisms and have them conditionally compiled into our code. This means that when our code rolls into production, we can have this code completely removed, so that there will be no runtime overhead to this logic. Note that I can specify both Boolean and PLS_INTEGER values through the special PLSQL_CCFLAGS compile parameter .

/* File on web: cc_debug_trace.sql */
ALTER SESSION SET PLSQL_CCFLAGS = 'oe_debug:true, oe_trace_level:10';
 
CREATE OR REPLACE PROCEDURE calculate_totals
IS
BEGIN
$IF $$oe_debug AND $$oe_trace_level >= 5
$THEN
   DBMS_OUTPUT.PUT_LINE ('Tracing at level 5 or higher'),
$END
   NULL;
END calculate_totals;
/

The DBMS_DB_VERSION built-in package offers a set of constants that give you absolute and relative information about the version of your installed database. The constants defined in the Oracle Database 10g Release 2 version of this package are shown in Table 20-2.

While this package was designed for use with conditional compilation, you can, of course, use it for your own purposes.

With each new release of the database, Oracle will add additional constants and will update the values returned by the VERSION and RELEASE constants.

Interestingly, you can write expressions that include references to as-yet undefined constants in the DBMS_DB_VERSION package. As long as they are not evaluated, as in the case below, they will not cause any errors. Here is an example:

$IF DBMS_DB_VERSION.VER_LE_10_2
$THEN
   Use this code.
$ELSEIF DBMS_DB_VERSION.VER_LE_11
   This is a placeholder for future.
$ENDIF

The following information (corresponding to the values in the USER_PLSQL_OBJECT_SETTINGS data dictionary view) is available via inquiry directives:

See the cc_plsql_parameters.sql file on the book’s web site for a demonstration that uses each of these parameters.

Oracle implicitly defines two very useful inquiry directives for use in $IF and $ERROR directives:

You can call DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to obtain current line numbers, but then you must also parse those strings to find the line number and program unit name. These inquiry directives provide the information more directly. Here is an example:

BEGIN
   IF l_balance < 10000
   THEN
      raise_error (
         err_name => 'BALANCE TOO LOW'
        ,failed_in => $$plsql_unit
        ,failed_on => $$plsql_line
      );
   END IF;
   ...
END;

Run cc_line_unit.sql to see a demonstration of using these last two directives.

Note that when $$PLSQL_UNIT is referenced inside a package, it will return the name of the package, not the individual procedure or function within the package.

Oracle offers a new initialization parameter, PLSQL_CCFLAGS, that you can use with conditional compilation. Essentially, it allows you to define name-value pairs, and the name can then be referenced as an inquiry directive in your conditional compilation logic. Here is an example:

ALTER SESSION SET PLSQL_CCFLAGS = 'use_debug:TRUE, trace_level:10';

The flag name can be set to any valid PL/SQL identifier, including reserved words and keywords (the identifier will be prefixed with $$, so there will be no confusion with normal PL/SQL code). The value assigned to the name must be one of the following: TRUE, FALSE, NULL, or a PLS_INTEGER literal.

The PLSQL_CCFLAGS value will be associated with each program that is then compiled in that session. If you want to keep those settings with the program, then future compilations with the ALTER...COMPILE command should include the REUSE SETTINGS clause.

Because you can change the value of this parameter and then compile selected program units, you can easily define different sets of inquiry directives for different programs.

Note that you can refer to a flag that is not defined in PLSQL_CCFLAGS; this flag will evaluate to NULL. If you enable compile-time warnings, this reference to an undefined flag will cause Oracle to report a PLW-06003: unknown inquiry directive warning (unless the source code is wrapped).

In the previous section, I started creating a grid of inputs and expected results for calls to betwnstr. I will now transform that grid into a delimited string that looks like this:

DECLARE
      test_grid   VARCHAR2 (1000) := '
betwnstr|1|start at 1|start at 1|abcdefgh;1;3|abc|eq|N
betwnstr|1|start at 3|start at 3|abcdefgh;3;6|cde|eq|N
betwnstr|1|null start|null start|abcdefgh;!null;2|null|isnull|Y
betwnstr|1|null end||abcdefgh;!3;!null|null|isnull|Y
betwnstr|1|null string||!null;1;2|NULL|isnull|Y
betwnstr|1|big start small end||abcdefgh;10;5|null|isnull|Y
betwnstr|1|end past string||abcdefgh;1;100|abcdefgh|eq|N';

I will then pass that string to a program in the utGen package, which will generate all of my test code for me:

BEGIN
   utgen.testpkg_from_string ('betwnstr',
      test_grid,
      output_type_in=> utgen.c_file,
      dir_in=> 'TEMP'
   );
END;

I then compile the ut_bewtnstr.pks and ut_betwnstr.pkb files that were generated:

SQL>@ut_betwnstr.pks
SQL> @ut_betwnstr.pkb

I am now ready to run my test, so I open a SQL*Plus session and issue this statement:

SQL>exec utplsql.test ('betwnstr')

I am then presented with this information:

>  FFFFFFF   AA     III  L      U     U RRRRR   EEEEEEE
>  F        A  A     I   L      U     U R    R  E
>  F       A    A    I   L      U     U R     R E
>  F      A      A   I   L      U     U R     R E
>  FFFF   A      A   I   L      U     U RRRRRR  EEEE
>  F      AAAAAAAA   I   L      U     U R   R   E
>  F      A      A   I   L      U     U R    R  E
>  F      A      A   I   L       U   U  R     R E
>  F      A      A  III  LLLLLLL  UUU   R     R EEEEEEE
.
 FAILURE: "betwnstr"
.
> Individual Test Case Results:
>
SUCCESS - EQ "start at 1" Expected "abc" and got "abc"
FAILURE - EQ "start at 3" Expected "cde" and got "cdef"
SUCCESS - ISNULL "null start" Expected "" and got ""
SUCCESS - ISNULL "null end" Expected "" and got ""
SUCCESS - ISNULL "null string" Expected "" and got ""
SUCCESS - ISNULL "big start small end" Expected "" and got ""
SUCCESS - EQ "end past string" Expected "abcdefgh" and got "abcdefgh"

Notice that utPLSQL shows me which of the test cases failed, what it expected, and what it received after running the test. So the first thing I do is go back to my test code (which in this case is simply a grid of test case inputs and outputs) and make sure I didn’t make any mistakes. I focus on this line:

betwnstr|1|start at 3|start at 3|abcdefgh;3;6|cde|eq|N

It doesn’t take me long to realize that the “cde” or expected results is wrong. It should be “cdef.” So I change my test case information, regenerate my test code, run my test, and then am delighted to see this on my screen:

SQL>exec utplsql.test ('betwnstr')
.
>    SSSS   U     U   CCC     CCC   EEEEEEE   SSSS     SSSS
>   S    S  U     U  C   C   C   C  E        S    S   S    S
>  S        U     U C     C C     C E       S        S
>   S       U     U C       C       E        S        S
>    SSSS   U     U C       C       EEEE      SSSS     SSSS
>        S  U     U C       C       E             S        S
>         S U     U C     C C     C E              S        S
>   S    S   U   U   C   C   C   C  E        S    S   S    S
>    SSSS     UUU     CCC     CCC   EEEEEEE   SSSS     SSSS
.
 SUCCESS: "betwnstr"

This is a very brief introduction to utPLSQL, but you can see that this framework automatically runs my test, and then tells me whether or not my test succeeded. It even reports on individual test cases.

utPLSQL was able to generate 100% of my test package for betwnstr, which is a bit of a special case in that it is a deterministic function (see Chapter 17 for more details on this characteristic). For most of the code you have written, you will be able to generate a starting point for your test package, but then complete it (and maintain it) manually.

utPLSQL doesn’t take all the pain out of building and running test code, but it provides a standardized process and a test harness from which you can run your tests and easily view results.

While there is a lot more to be said and demonstrated about utPLSQL, you should now have enough of an understanding of it to decide whether it might be of interest to you. To learn more about utPLSQL, the utAssert assertion routines, and the rest of this unit-testing framework, visit the project home for utPLSQL at:

You can also download utPLSQL along with a graphical interface to the test framework, named Ounit, at:

Both products are free.

Onxo also offers a graphical interface to utPLSQL and adds test package generation capabilities as well. Check it out at:

When faced with a bug, you become a whirlwind of frenzied activity. Even though the presence of an error indicates that you did not fully analyze the problem and figure out how the program should solve it, you do not now take the time to understand the program. Instead you place MESSAGE statements (in Oracle Forms) or SRW.MESSAGE statements (in Oracle Reports) or DBMS_OUTPUT.PUT_LINE statements (in stored modules) all over your program in the hopes of extracting more clues.

You do not save a copy of the program before you start making changes because that would take too much time; you are under a lot of pressure right now, and you are certain that the answer will pop right out at you. You will just remove your debug statements later.

You spend lots of time looking at information that is mostly irrelevant. You question everything about your program, even though most of it uses constructs you’ve employed successfully for years.

You skip lunch but make time for coffee, lots of coffee, because it is free and you want to make sure your concentration is at the most intense level possible. Even though you have no idea what is causing the problem, you think that maybe if you try this one change, it might help. You make the change and take several minutes to compile, generate, and run through the test case, only to find that the change didn’t help. In fact, it seemed to cause another problem because you hadn’t thought through the impact of the change on your application.

So you back out of that change and try something else in hopes that it might work. But several minutes later, you again find that it doesn’t. A friend, noticing that your fingers are trembling, offers to help. But you don’t know where to start explaining the problem because you don’t really know what is wrong. Furthermore, you are kind of embarrassed about what you’ve done so far (turned the program into a minefield of tracing statements) and realize you don’t have a clean version to show your friend. So you snap at the best programmer in your group and call your family to let them know you aren’t going to be home for dinner that night.

Why? Because you are determined to fix that bug!

You execute your report, and it comes up empty. You spent the last hour making changes both in the underlying data structures and in the code that queries and formats the data. You are certain, however, that your modifications could not have made the report disappear.

You call your internal support hotline to find out if there is a network problem, even though File Manager clearly shows access to network drives. You further probe as to whether the database has gone down, even though you just connected successfully. You spend another 10 minutes of the support analyst’s time running through a variety of scenarios before you hang up in frustration.

“They don’t know anything over there,” you fume. You realize that you will have to figure this one out all by yourself. So you dive into the code you just modified. You are determined to check every single line until you find the cause of your difficulty. Over the course of the next two hours, you talk aloud to yourself—a lot.

“Look at that! I called the stored procedure inside an IF statement. I never did that before. Maybe you can’t call stored programs that way.” So you remove the IF statement and instead use a GOTO statement to perform the branching to the stored procedure. But that doesn’t fix the problem.

“My code seems fine. But it calls this other routine that Joe wrote ages ago.” Joe has since moved on, making him a ripe candidate for the scapegoat. “It probably doesn’t work anymore; after all, we did upgrade to a new voicemail system.” So you decide to perform a standalone test of Joe’s routine, which hasn’t changed for two years and has no interface to voicemail. But his program seems to work fine—when it’s not run from your program.

Now you are starting to get desperate. “Maybe this report should only run on weekends. Hey, can I put a local module in an anonymous block? Maybe I can use only local modules in procedures and functions! I think maybe I heard about a bug in this tool. Time for a workaround ... "

You get angry and begin to understand why your eight-year-old hits the computer monitor when he can’t beat the last level of Ultra Mystic Conqueror VII. And just as you are ready to go home and take it out on your dog, you realize that you are connected to the development database, which has almost no data at all. You switch to the test instance, run your report, and everything looks just fine.

Except, of course, for that GOTO and all the other workarounds you stuck in the report ...

The single most effective thing you can do to minimize the time spent debugging your code is to use a source code debugger. One is now available in just about every PL/SQL Integrated Development Environment (IDE). If you are using Quest’s Toad or SQL Navigator , Allround Automations’ PL/SQL Developer, or Oracle JDeveloper (or any other such GUI tool), you will be able to set visual breakpoints in your code with the click of a mouse, step through your code line by line, watch variables as they change their values, and so on.

The other tips in this section apply whether or not you are using a GUI-based debugger, but there is no doubt that if you are still debugging the old-fashioned way (inserting calls to DBMS_OUTPUT.PUT_LINE in dozens of places in your code), you are wasting a lot of your time. (Unfortunately, if your code is deployed at some customer site, debugging with a GUI tool is not always possible, in which case you usually have to resort to some sort of logging mechanism.)

Gather as much data as possible about when, where, and how the error occurred. It is very unlikely that the first occurrence of an error will give you all the information you will want or need to figure out the source of that error. Upon noticing an error, the temptation is to show off one’s knowledge of the program by declaring, “Got it! I know what’s going on and exactly how to fix it.” This can be very gratifying when it turns out that you do have a handle on the problem, and that may be the case for simple bugs. Some problems can appear simple, however, and turn out to require extensive testing and analysis. Save yourself the embarrassment of pretending (or believing) that you know more than you actually do. Before rushing to change your code, take these steps:

The more information you gather about the problem at hand, the easier it will be to solve that problem. It is worth the extra time to assemble the evidence. So even when you are absolutely sure you are on to that bug, hold off and investigate a little further.

Symbolic logic is the lifeblood of programmers. No matter which programming language you use, the underlying logical framework is a constant. PL/SQL has one particular syntax. The C language uses different keywords, and the IF statement looks a little different. The elegance of LISP demands a very different way of building programs. But underneath it all, symbolic logic provides the backbone on which you hang the statements that solve your problems.

The reliance on logical and rational thought in programming is one reason that it is so easy for a developer to learn a new programming language. As long as you can take the statement of a problem and develop a logical solution step by step, the particulars of a language are secondary.

With logic at the core of our being, it amazes me to see how often we programmers abandon this logic and pursue the most irrational path to solving a problem. We engage in wishful thinking and highly superstitious, irrational, or dubious thought processes. Even though we know better—much better—we find ourselves questioning code that conforms to documented functionality, that has worked in the past, and that surely works at that moment. This irrationality almost always involves shifting the blame from oneself to the “other”—the computer, the compiler, Joe, the word processor, whatever. Anything and anybody but our own pristine selves!

When you attempt to shift blame, you only put off solving your problem. Computers and compilers may not be intelligent, but they’re very fast and very consistent. All they can do is follow rules, and you write the rules in your program. So when you uncover a bug in your code, take responsibility for that error. Assume that you did something wrong—don’t blame the PL/SQL compiler, Oracle Forms, or the text editor.

If you do find yourself questioning a basic element or rule in the compiler that has always worked for you in the past, it is time to take a break. Better yet, it is time to get someone else to look at your code. It is amazing how another pair of eyes can focus your own analytical powers on the real causes of a problem.

So you have a pile of data and all the clues you could ask for in profiling the symptoms of your problem. Now it is time to analyze that data. For many people, analysis takes the following form: “Hmm, this looks like it could be the answer. I’ll make this change, recompile, and try it to see if it works.”

What’s wrong with this approach? When you try a solution to see what will happen, what you are really saying is:

To truly solve a problem, you must completely analyze the cause of the problem. Once you understand why the problem occurs, you have found the root cause and can take the steps necessary to make the problem go away in all circumstances.

When you identify a potential solution, perform a walk-through of your code based on that change. Don’t execute your form. Examine your program, and mentally try out different scenarios to test your hypothesis. Once you are certain that your change actually does address the problem, you can then perform a test of that solution. You won’t be trying anything; you will be verifying a fix.

Analyze your bug fully before you test solutions. If you say to yourself, “Why don’t I try this?” in the hope that it will solve the problem, then you are wasting your time and debugging inefficiently.

We are often our own biggest obstacles when it comes to sorting out our problems, whether a program bug or a personal crisis. When you are stuck on the inside of a problem, it is hard to maintain an objective distance and take a fresh look.

When you are making absolutely no progress and feel that you have tried everything, try these two radical techniques:

When I have struggled with a bug for any length of time without success, I not only become ineffective, I also tend to lose perspective. I pursue irrational and superstitious leads. I lose track of what I have already tested and what I have assumed to be right. I get too close to the problem to debug it effectively.

My frustration level usually correlates closely to the amount of time I have sat in my ergonomic chair and perched over my wrist-padded keyboard and stared at my low-radiation screen. Often the very simple act of stepping away from the workstation will clear my head and leave room for a solution to pop into place. Did you ever wake up the morning after a very difficult day at work to find the elusive answer sitting there at the end of your dream?

Make it a rule to get up and walk around at least once an hour when you are working on a problem—heck, even when you are writing your programs. Give your brain a chance to let its neural networks make the connections and develop new options for your programming. There is a whole big world out there. Even when your eyes are glued to the monitor and your source code, the world keeps turning. It never hurts to remind yourself of the bigger picture, even if that only amounts to taking note of the weather outside your air-conditioned cocoon.

Even more effective than taking a break is asking another person to look at your problem. There is something entirely magical about the dynamic of adding another pair of eyes to the situation. You might struggle with a problem for an hour or two, and finally, at the exact moment that you break down and explain the problem to a coworker, the solution will jump out at you. It could be a mismatch on names, a false assumption, or a misunderstanding of the IF statement logic. Whatever the case, chances are that you yourself will find it (even though you couldn’t for the last two hours) as soon as you ask someone else to find it for you.

And even if the error does not yield itself quite so easily, you still have lots to gain from the perspective of another person who (a) did not write the code and has no subconscious assumptions or biases about it, and (b) isn’t mad at the program.

Other benefits accrue from asking for help. You improve the self-esteem and self- confidence of other programmers by showing that you respect their opinions. If you are one of the best developers in the group, then your request for help demonstrates that you, too, sometimes make mistakes and need help from the team. This builds the sense (and the reality) of teamwork, which will improve the overall development and testing efforts on the project.

One of my biggest problems when I debug my code is that I am overconfident about my development and debugging skills, so I try to address too many problems at once. I make five or ten changes, rerun my test, and get very unreliable and minimally useful results. I find that my changes cause other problems (a common phenomenon until a program stabilizes, and a sure sign that lots more debugging and testing is needed), that some, but not all, of the original errors are gone, and that I have no idea which changes fixed which errors and which changes caused new errors.

In short, my debugging effort is a mess, and I have to back out of changes until I have a clearer picture of what is happening in my program.

Unless you are making very simple changes, you should fix one problem at a time and then test that fix. The amount of time it takes to compile, generate, and test may increase, but in the long run you will be much more productive.

Another aspect of incremental testing and debugging is performing unit tests on individual modules before you test a program that calls these various modules. If you test the programs separately and determine that they work, when you debug your application as a whole (in a system test), you do not have to worry about whether those modules return correct values or perform the correct actions. Instead, you can concentrate on the code that calls the modules. (See the earlier section "Testing PL/SQL Programs,” for more on unit testing.)

You will also find it helpful to come up with a system for keeping track of your troubleshooting efforts. Dan Clamage, a reviewer for this book, reports that he maintains a simple text file with running commentary of his efforts to reproduce the problem and what he has done to correct it. This file will usually include any SQL written to analyze the situation, setup data for test cases, a list of the modules examined, and any other items that may be of interest in the future. With this file in place, it’s much easier to return at any time (e.g., after you have had a good night’s sleep and are ready to try again) and follow your original line of reasoning.

This function returns the execution call stack (the sequence of program calls) down to the point at which you call the function. Here is an example of the formatting of this stack string:

----- PL/SQL Call Stack -----
object   line    object
handle   number  name
 
88ce3f74    8  package STEVEN.VALIDATE_REQUEST
88e49fc4    2  function STEVEN.COMPANY_TYPE
88e49390    1  procedure STEVEN.CALC_NET_WORTH
88e2bd20    1  anonymous block

One of the best places to use this function is within an exception handler, as in:

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (
         DBMS_UTILITY.FORMAT_CALL_STACK);
END;

Better yet, grab this information and write it to your log table, so that the support and debug teams can immediately see how you got to the point where the problem reared its ugly head.

There is, by the way, one big problem with the exception section above: if your call stack is deep, the formatted string will exceed 255 characters in length. Before Oracle Database 10g Release 2, DBMS_OUTPUT.PUT_LINE would raise an exception in such cases. To avoid this problem, you might consider using Darko Egersdorfer’s callstack package, found in the callstack.pkg file on the book’s web site.

This package may not have been installed automatically with the rest of the built-in packages. To determine whether DBMS_TRACE is present, connect to SYS (or another account with SYSDBA privileges) and execute this command:

BEGIN DBMS_TRACE.CLEAR_PLSQL_TRACE; END;

If you see this error:

PLS-00201: identifier 'DBMS_TRACE.CLEAR_PLSQL_TRACE' must be declared

then you must install the package. To do this, remain connected as SYS (or another account with SYSDBA privileges), and run the following files in the order specified:

The following programs are available in the DBMS_TRACE package:

To trace execution of your PL/SQL code, you must first start the trace with a call to:

DBMS_TRACE.SET_PLSQL_TRACE (trace_level INTEGER);

in your current session, where trace_level is one of the following values:

To turn on tracing from all programs executed in your session, issue this call:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_calls);

To turn on tracing for all exceptions raised during the session, issue this call:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_exceptions);

You then run your code. When you are done, you stop the trace session by calling:

DBMS_TRACE.CLEAR_PLSQL_TRACE;

You can then examine the contents of the trace file. The names of these files are generated by Oracle; you will usually look at the modification dates to figure out which file to examine. The location of the trace files is discussed in the later section, "Format of collected data.”

Note that you cannot use PL/SQL tracing with the shared server (formerly known as the multithreaded server, or MTS).

The trace files produced by DBMS_TRACE can get really big. You can focus the output by enabling only specific programs for trace data collection. Note that you cannot use this approach with remote procedure calls.

To enable a specific program for tracing, you can alter the session to enable any programs that are created or replaced in the session. To take this approach, issue this command:

ALTER SESSION SET PLSQL_DEBUG=TRUE;

If you don’t want to alter your entire session, you can recompile a specific program unit in debug mode as follows (not applicable to anonymous blocks):

ALTER [PROCEDURE | FUNCTION | PACKAGE BODY]program_name COMPILE DEBUG;

After you have enabled the programs in which you’re interested, the following call will initiate tracing just for those program units:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_calls);

You can also restrict the trace information to only those exceptions raised within enabled programs with this call:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_exceptions);

If you request tracing for all programs or exceptions and also request tracing only for enabled programs or exceptions, the request for “all” takes precedence.

The SET_PLSQL_TRACE procedure can do more than just determine which information will be traced. You can also request that the tracing process be paused and resumed. The following statement, for example, requests that no information be gathered until tracing is resumed:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_pause);

DBMS_TRACE will write a record to the trace file to show when tracing was paused and/or resumed.

Use the DBMS_TRACE.trace_limit constant to request that only the last 8,192 trace events of a run be preserved. This approach helps ensure that you can turn tracing on without overwhelming the database with trace activity. When the trace session ends, only the last 8,192 records are saved.

If you request tracing only for enabled program units and the current program unit is not enabled, no trace data is written. If the current program unit is enabled, call tracing writes out the program unit type, name, and stack depth.

Exception tracing writes out the line number. Raising an exception records trace information on whether the exception is user-defined or predefined, and records the exception number in the case of predefined exceptions. If you raise a user-defined exception, you will always see an error code of 1.

Here is an example of the output from a trace of the showemps procedure:

*** 1999.06.14.09.59.25.394
*** SESSION ID:(9.7) 1999.06.14.09.59.25.344
------------ PL/SQL TRACE INFORMATION -----------
Levels set :  1
Trace:  ANONYMOUS BLOCK: Stack depth = 1
Trace:   PROCEDURE SCOTT.SHOWEMPS: Call to entry at line 5 Stack depth = 2
Trace:    PACKAGE BODY SYS.DBMS_SQL: Call to entry at line 1 Stack depth = 3
Trace:     PACKAGE BODY SYS.DBMS_SYS_SQL: Call to entry at line 1 Stack depth = 4
Trace:     PACKAGE BODY SYS.DBMS_SYS_SQL: ICD vector index = 21 Stack depth = 4
Trace:    PACKAGE PLVPRO.P: Call to entry at line 26 Stack depth = 3
Trace:    PACKAGE PLVPRO.P: ICD vector index = 6 Stack depth = 3
Trace:    PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3
Trace:    PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3
Trace:     PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 4

Oracle Database 10g Release 1 introduced an optimizing compiler for PL/SQL programs. The default optimization level of 2 takes the most aggressive approach possible in terms of transforming your code to make it run faster. You should use this default level unless compilation time is unacceptably slow, and you are not seeing benefits from optimization. See the "The Optimizing Compiler" section in this chapter for detailed information.

The BULK COLLECT statement retrieves multiple rows of data through either an implicit or an explicit query with a single round trip to and from the database. BULK COLLECT reduces the number of context switches between the PL/SQL and SQL engines and thereby reduces the overhead of retrieving data. Rather than using a cursor FOR loop or other row-by-row querying mechanism, switch to BULK COLLECT for a dramatic improvement in performance. See the "BULK COLLECT" section in Chapter 15 for more about this feature.

As with BULK COLLECT, FORALL greatly reduces context switching between the PL/SQL and SQL engines, but this time for updates, inserts, and deletes. You can expect to see an order of magnitude (or greater) improvement in performance for multiple-row DML execution with FORALL. See the "Bulk DML with the FORALL Statement" section in Chapter 14 for detailed information.

The NOCOPY parameter hint requests that the PL/SQL runtime engine pass an IN OUT argument by reference rather than by value. This can speed up the performance of your programs, because by-reference arguments are not copied within the program unit. When you pass large, complex structures like collections, records, or objects, this copy step can be expensive. See the "The NOCOPY Parameter Mode Hint" section in Chapter 17.

When you declare an integer variable as PLS_INTEGER, it will use less memory than INTEGER and rely on machine arithmetic to get the job done more efficiently. In a program that requires intensive integer computations, simply changing the way that you declare your variables could have a noticeable impact on performance. See the section "The PLS_INTEGER Type" in Chapter 9 for a more detailed discussion.

Oracle Database 10g introduces two, new floating-point types: BINARY_FLOAT and BINARY_DOUBLE. These types conform to the IEEE 754 floating-point standard and use native machine arithmetic, making them more efficient than NUMBER or INTEGER variables. See “The BINARY_FLOAT and BINARY_DOUBLE Types” section in Chapter 9.

Whenever you reference any single element in a package for the first time in your session, the entire package is cached in the shared memory pool. Any other calls to programs in the package require no additional disk I/O, thereby improving the performance of calling those programs. Group related programs into a package to take advantage of this feature. See the "Packaging to improve memory use and performance" section in Chapter 23 for details.

Pin frequently accessed programs in the shared memory pool with the DBMS_SHARED_POOL.PIN procedure . A pinned program will not be flushed out of the pool using the default least-recently-used algorithm. This guarantees that the code will already be present when it is need. See the “What to Do if You Run Out of Memory” section in Chapter 23.