 |
|
PL/SQL Runtime Errors
Oracle Tips by
Burleson
|
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.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.
For more information concerning dbms_output, see
here.
|
