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

 

 

   
 

Logging Errors
Oracle Tips by Burleson
 

The best use of the OTHERS exception handler is to record that an error has occurred and to prevent damage to the data as a result of the error. The exception handler in Listing 8.8 does that by recording the event to a SYSTEM_ERRORS table.

Listing 8.8 Using the OTHERS exception handler to log an error.

CREATE OR REPLACE
PROCEDURE Calculate_Student_Grades
 
IS
 
   CURSOR Active_Students_cur
   IS
   SELECT ssn
   FROM   STUDENTS
   WHERE  graduation_date IS NOT NULL;
 
   vCurrentSSN                STUDENTS.ssn%TYPE;
   nNewGPA                    STUDENTS.overall_gpa%TYPE;
   iTracePoint                integer;
 
BEGIN
   FOR Active_Student_rec IN Active_Students_cur LOOP
       iTracePoint := 1;
 
       SELECT sum (course_hours),
              sum (decode (course_grade, 'A', 4,
                                         'B', 3,
                                         'C', 2,
                                         'D', 1))
       INTO   iTotalHours, iTotalCredits
       FROM   ENROLLED_CLASSES
       WHERE  ssn = Active_Student_rec.ssn
       AND    nvl (credit_flag, 'Y') = 'Y'
       AND    nvl (audit_flag,  'N') = 'N'
       AND    course_complete <= SYSDATE;
 
       iTracePoint := 2;
 
       nNewGPA := iTotalCredits / iTotalHours;
 
       iTracePoint := 3;
 
       UPDATE STUDENTS
       SET    overall_gpa = nNewGPA
       WHERE  ssn = Active_Student_rec.ssn;
   END LOOP;
 
   iTracePoint := 4;
 
   COMMIT;
 
EXCEPTION
   WHEN OTHERS THEN
        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 Calculate_Student_Grades;
/

Notice the steps taken by the exception handler and the order in which they occur:

1.  Roll back any pending changes to the database.

2.  Record the error message, location, and any data that could be of use when attempting to resolve the error.

3.  Commit the data about the error message.

In this example, the SYSTEM_ERRORS table is a custom table implemented to aid in debugging runtime errors. This isn’t one of Oracle’s data dictionary tables.

Of course, it’s possible that there may be other changes pending to the database that didn’t originate in this object. In this event, the OTHERS exception handler should still record the information about the error before raising the exception to the calling object. The decision about whether or not to roll back the changes can then be made by the calling object.

Your exception handlers will often include calls to some built-in error handling functions in PL/SQL.

    
               
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