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 Useful Functions
Oracle Tips by Burleson
 

PL/SQL provides two useful functions that allow you to identify errors: SQLCODE() and SQLERRM(). These functions are specific to PL/SQL and canít be used in your SQL statements; attempting to do so will result in an error. However, the output of these functions can be assigned to variables in your PL/SQL blocks. Letís take a look at each function.

Using the SQLCODE() Function

The SQLCODE() function is used to return the number of the most recent Oracle error message during the execution of a PL/SQL block. For instance, a reference to a nonexistent table or view causes Oracle error ORA-00942 to occur. For this error, the SQLCODE() function would return:

-00942

Listing 8.9 illustrates how the SQLCODE() function can be used in your exception handlers.

Listing 8.9 Calling the SQLCODE() function in an exception handler.

EXCEPTION
   WHEN OTHERS THEN
        IF (SQLCODE = -942) THEN
           RAISE xMISSING_TABLE;
 
        ELSE
           ROLLBACK;
 
           INSERT
           INTO   SYSTEM_ERRORS
                  (error_time,
                   error_parameters,
                   display,
                   error_text,
                   error_number,
                   error_object)
           VALUES (SYSDATE,
                   Active_Student_rec.ssn,
                   'N',
                   SQLERRM || ' at tracepoint ' || to_char (iTracePoint),
                   SQLCODE,
                   'Calculate_Student_Grades');
 
           COMMIT;
        END IF;
END;

The error numbers returned by this function are always negative.

Using the SQLERRM() Function

The SQLERRM() function returns the complete text of the last Oracle error message to occur during the execution of a block of PL/SQL code. Calling this function is quite simple:

vErrorText := SQLERRM;

Calling this function when no errors have occurred returns:

ORA-0000: normal, successful completion

Summary

Debugging is a skill that relies heavily on a developerís experience and familiarity with the code. Debugging is the largest part of a developerís job and probably the most tedious aspect. This chapter covers several techniques that can ease the burden of debugging your code if you plan ahead when youíre writing the code.

     
               
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

††
 

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