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



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.

PROCEDURE Calculate_Student_Grades
   CURSOR Active_Students_cur
   SELECT ssn
   WHERE  graduation_date IS NOT NULL;
   vCurrentSSN                STUDENTS.ssn%TYPE;
   nNewGPA                    STUDENTS.overall_gpa%TYPE;
   iTracePoint                integer;
   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
       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;
       SET    overall_gpa = nNewGPA
       WHERE  ssn = Active_Student_rec.ssn;
   iTracePoint := 4;
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