Oracle dbms_output tips

Runtime errors are errors that occur while code is executing. These errors can arise due to data problems or code problems. For instance, attempting to assign a 31-character string to a varchar2 (30) variable will cause a runtime error.

Most approaches to dealing with runtime errors utilize the DBMS_Output package to isolate the location of an error so that a developer can correct the problem. This package provides an excellent debugging tool when used properly.

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.