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

 

 

   
 

Handling Exceptions Cleanly During Execution
Oracle Tips by Burleson
 

Using exceptions thoughtfully can save you a lot of time that you would otherwise spend writing error handling code. Just like many other coding techniques, the best way to use the technique is to design with the technique in mind. While this does add some overhead to the design process, using exception handlers (particularly the OTHERS exception handler) is essential to writing effective code.

The OTHERS Exception Handler

The OTHERS exception handler serves as a catch-all exception handler, handling any error that falls through any other exception handling that you have in place. This exception handler should always be the last exception handler in your block of code.

This exception handler is a powerful tool if used properly. If used improperly or carelessly, the OTHERS exception handler will mask errors and make your debugging work more difficult than it needs to be. Listing 8.7 shows how the OTHERS exception handler can be misused.

Listing 8.7 Misusing the OTHERS exception handler.

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;
END Calculate_Student_Grades;
/

In this example, the exception handler doesn’t do anything that will help the developer debug the application if something goes wrong. In fact, the exception handler completely obscures the fact that something is going wrong.

Preventing data from being altered when an error occurs is an admirable goal, but at the end of the semester when grades go out each student will have remarkably maintained the status quo and the developer (or someone who has taken over the responsibilities) will find themselves in a very hot spot. At the very least, the developer in this case should have recorded that an error occurred.

   
               
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