Previous Table of Contents Next


Using The DBMS_Output Package

The DBMS_Output package was first introduced with Oracle7 to allow output to the SQL buffer in SQL*Plus from PL/SQL blocks. The package was intended primarily as a debugging tool, and it has served that purpose admirably (although it is now being supplanted by step-through debuggers available in Oracle’s Procedure Builder and several other third-party tools).

In order to use the DBMS_Output package for debugging, you must issue the

set serveroutput on

command in SQL*Plus. This command instructs SQL*Plus to collect the contents of the buffer after executing a PL/SQL block or stored PL/SQL object.

The size of this buffer defaults to 2,000 characters. For practical purposes, this limit is far too low. Fortunately, you can use the

set serveroutput on size n

command to specify the size of the buffer. In this command, n specifies the buffer size and can range from 2,000 characters to an upper limit of 1 million characters. A million characters is more than sufficient to debug any modularized block of code.

Debugging with the DBMS_Output package involves mostly calls to the DBMS_Output.Put_Line() procedure. This procedure writes a line to the SQL buffer. When a block of PL/SQL code finishes executing, the contents of the SQL buffer are displayed.

Listing 8.5 is an excerpt from the debugging version of the Build_SUID_Matrix package (the final version of the package can be found on the CD).

Listing 8.5 An excerpt of debugging code from the Build_SUID_Matrix package.

DBMS_Output.Put_Line ('Fetch ObjectSourceCode_cur');

FETCH ObjectSourceCode_cur INTO ObjectSourceCode_rec;
EXIT WHEN ObjectSourceCode_cur%NOTFOUND;

--
-- Initialize variables.
--
iStringLen        := 0;
iStringPos        := 0;

--
-- Clean the line of code before processing it.
--
DBMS_Output.Put_Line ('Call CleanLineOfSource');
vLine      := CleanLineOfSource (ObjectSourceCode_rec.text);
DBMS_Output.Put_Line (vLine);

--
-- If the line contains the string 'DELETE ', this might be a delete
-- operation.
--
DBMS_Output.Put_Line ('Check for DELETE ');
iStringPos := instr (vLine, 'DELETE ');
iStringLen := length (vLine);

--
-- Test the line to determine if the 'DELETE ' string is
--    A) inside a comment
--    B) part of an identifier
--
DBMS_Output.Put_Line ('Is the string inside a comment?');

IF ((instr (vLine, '--') > 0)
    AND
    (instr (vLine, '--') < iStringPos)) THEN
      iStringPos := 0;
END IF;

--
-- Is the string inside a comment?
--
DBMS_Output.Put_Line ('Check for string following a */');

IF ((instr (vLine, '/*') > 0)
    AND
    (iStringPos > instr (vLine, '/*'))) THEN
      iStringPos := 0;
END IF;

--
-- Is the string inside a comment?
--
DBMS_Output.Put_Line ('Check for */ without a preceding /*');

IF ((instr (vLine, '*/') > 0) AND (instr (vLine, '/*') = 0)) THEN
   iStringPos := 0;
END IF;

--
-- Is the string inside an identifier?
--
DBMS_Output.Put_Line ('Check for part of identifier');
IF ((instr (vLine, '_DELETE ') = (iStringPos - 1))
    AND
    (instr (vLine, '_DELETE ') > 0)) THEN
       iStringPos := 0;
END IF;

--
-- If the delete is beyond the first character of the line,
-- either it is poorly written code or it is a comment.
--
DBMS_Output.Put_Line ('The delete is past the first character');

IF (iStringPos > 1) THEN
   iStringPos := 0;
END IF;

--
-- If the line has passed all the false positive tests, go ahead
-- and display the table name.
--
IF (iStringPos > 0) THEN
   vParsedString := substr (vLine, (iStringPos + 6));
END IF;

--
-- If the line has passed the false positive tests, check to see
-- if it contains a 'FROM' clause.  If so, remove the clause from
-- the string.
--
IF (iStringPos > 0) THEN
   iStringPos := instr (vParsedString, 'FROM ');

   IF (iStringPos > 0) THEN
      vParsedString := substr (vParsedString, (iStringPos + 5));
   END IF;

   --
   -- Remove the semicolon at the end of the line.
   --
   DBMS_Output.Put_Line ('Replace ; at the end of the line');
   vParsedString := replace (vParsedString, ';', '');

   --
   -- Call the UpdateMatrix procedure to perform the write to the
   -- SUID_MATRIX table.
   --
   UpdateMatrix (vParsedString,
                 vOwner,
                 vObject,
                 'DELETE');
END IF;

Each of the calls to the DBMS_Output.Put_Line() procedure indicates the progress of the procedure. A string of text is passed as the procedure’s lone parameter.

The runtime error must always occur after the last message that was delivered to the buffer. Isolating the error is now a simple matter of determining which statements occurred after the message.

Locating Runtime Errors

Locating a runtime error in a complex piece of code (or one that runs through several pieces of code) is a time-consuming and grueling task. There are two basic ways of isolating a runtime error inside a block of code:

  Ignoring exceptions—allowing errors to rise to the highest level, and debugging based on line number and error text.
  Using a tracepoint variable to keep track of the object’s execution.

Each of these methods is discussed in detail in the following sections. Keep in mind that each of these methods is an extreme approach and that these approaches can be blended to best suit your programming style.

Ignoring Exceptions

The basic idea of this approach is that the line and position values returned by Oracle are the most effective method of isolating a problem. Consequently, the developer ignores the use of exception handlers and expects each exception to be raised to the highest possible level.

One significant effect of this approach is that operations are always interrupted if an error occurs while executing a stored PL/SQL object. This error is raised to the highest possible level. Unfortunately, this makes for a very hostile environment for users because every error that occurs interrupts and potentially destroys work.

When an error occurs, the developer collects the error text from the user and begins stepping through the object by hand. The developer must figure out what parameter values were passed to the object and then execute the code to reproduce and isolate the error.


Previous Table of Contents Next