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

 

 

   
 

Exception Handling
Oracle Tips by Burleson
 

Itís very common for developers to flag error conditions and handle the condition through the use of IF-THEN logic. Listing 10.5 illustrates the use of this approach.

Listing 10.5 Using IF-THEN logic to flag errors.

DECLARE
   bAidAmountOk  boolean;
   vLastName     varchar2 (20);
   vFirstName    varchar2 (20);
   nGPA          number   (3,2);
   nSemesterGPA  number   (3,2);
   vSSN          varchar2 (9);
   nFinanceNum   number   (5);
   nTotalAid     number   (7,2);
 
   CURSOR Students_cur
   IS
   SELECT ssn, first_name, last_name, financing_num,
          overall_gpa
   FROM   STUDENTS
   WHERE  overall_gpa < 2.5;
 
BEGIN
   FOR Students_rec IN Students_cur LOOP
      bAidAmountOk := FALSE;
 
      vFirstName  := Students_rec.first_name;
      vLastName   := Students_rec.last_name;
      nGPA        := Students_rec.overall_gpa;
      nFinanceNum := Students_rec.financing_num;
      vSSN        := Students_rec.ssn;
 
      SELECT total_aid
      INTO   nTotalAid
      FROM   STUDENT_FINANCIAL_AID
      WHERE  financing_num = nFinanceNum;
 
      IF (nTotalAid < $1000) THEN
         bAidAmountOk := TRUE;
      END IF;
 
      IF NOT bAidAmountOk THEN
         nSemesterGPA := Get_Semester_GPA (vStudentSSN => vSSN);
 
         IF (nSemesterGPA > 3.0) THEN
            bAidAmountOk := TRUE;
         END IF;
      END IF;
   END LOOP;
END;

This example uses the boolean variable bAidAmountOk to keep track of a condition throughout the processing of each student record. The highlighted statements deal directly with keeping track of this condition throughout the loop.

This approach does have an impact on performance. Multiple instructions are used to test for the error condition. Each instruction requires CPU cycles to complete. A much better approach involves the use of exceptions to avoid wasting CPU cycles, as shown in Listing 10.6.

Listing 10.6 Using exception handlers to improve performance.

DECLARE
   vLastName      varchar2 (20);
   vFirstName     varchar2 (20);
   nGPA           number   (3,2);
   nSemesterGPA   number   (3,2);
   vSSN           varchar2 (9);
   nFinanceNum    number   (5);
   nTotalAid      number   (7,2);
   xAID_AMOUNT_OK EXCEPTION;
 
   CURSOR Students_cur
   IS
   SELECT ssn, first_name, last_name, financing_num,
          overall_gpa
   FROM   STUDENTS
   WHERE  overall_gpa < 2.5;
 
BEGIN
   FOR Students_rec IN Students_cur LOOP
      BEGIN
         vFirstName  := Students_rec.first_name;
         vLastName   := Students_rec.last_name;
         nGPA        := Students_rec.overall_gpa;
         nFinanceNum := Students_rec.financing_num;
         vSSN        := Students_rec.ssn;
 
         SELECT sum (total_aid)
         INTO   nTotalAid
         FROM   STUDENT_FINANCIAL_AID
         WHERE  financing_num = nFinanceNum;
 
         IF (nTotalAid < $1000) THEN
            RAISE xAID_AMOUNT_OK;
         END IF;
 
         nSemesterGPA := Get_Semester_GPA (vStudentSSN => vSSN);
 
         IF (nSemesterGPA > 3.0) THEN
            RAISE xAID_AMOUNT_OK;
         END IF;
 
      EXCEPTION
         WHEN xAID_AMOUNT_OK THEN
              NULL;
      END;
   END LOOP;
END;

In this example, the xAID_AMOUNT_OK exception is explicitly raised inside the loop to allow execution to skip the instructions that occur after the studentís GPA is checked. This probably results in a gain of several CPU cycles being freed up. The highlighted statements in the example are used for this exception handling.

PL/SQLís exception handling is very performance efficient. When an exception is raised, all subsequent instructions within the block are bypassed so the exception can be handled by an exception handler. You can utilize this built-in performance boost by thinking about the organization of your code before you write it and planning to use user-defined exceptions to skip code that you donít need to execute.

Summary

Performance tuning is one of the most grueling aspects of any Oracle developerís job. Often, it will take hours to achieve acceptable performance from a complex query. The tips presented in this chapter will provide a starting point for this type of work, which you will have to do at some point in your career.

             
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