Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

 

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter