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

 

 

   
 

Using the RAISE Statement
Oracle Tips by Burleson
 

You can cause exceptions to be raised in your code by using the RAISE statement. Listing 4.23 illustrates the use of the statement.

Listing 4.23 Using the RAISE statement in your code.

BEGIN
   IF <some condition> THEN
      RAISE xABORT_PROCEDURE;
   END IF;
 
EXCEPTION
  WHEN xABORT_PROCEDURE THEN
       ROLLBACK;
END;

Using User-Defined Exceptions
Itís a good idea not to explicitly raise the predefined Oracle exceptions. Doing so confuses the debugging process. Consider the following example:

FOR StudentClasses_rec IN StudentClasses_cur LOOP

  SELECT course_hours
  INTO   iCourseHours
  FROM   CLASSES
  WHERE  course_number = iCourseID;
 
  iTotalClasses := iTotalClasses + 1;
END LOOP;
 
IF (iTotalClasses = 0) THEN
  RAISE NO_DATA_FOUND;
END IF;

If your code looks like this example and youíre receiving a NO_DATA_FOUND exception, you canít be certain where the exception is originating without doing some extra debugging work. It would be much better to define an appropriately named user-defined exception, say xSTUDENT_HAS_NO_CLASSES, and explicitly raise that exception instead.

If you must raise an exception under a certain condition, take advantage of the power and flexibility of user-defined exceptions.


Using Raise_Application_Error()

Oracle provides the Raise_Application_Error() procedure to allow you to raise custom error numbers within your applications. You can generate errors and their associated text starting with -20000 and proceeding through -20999 (a grand total of 1,000 error numbers that you can use). Listing 4.24 illustrates the use of the Raise_Application_Error() procedure.

Listing 4.24 Using the Raise_Application_Error() procedure.

DECLARE
  Balance   integer := 24;
 
BEGIN
  IF (nBalance <= 100) THEN
      Raise_Application_Error (-20343, 'The balance is too low.');
  END IF;
END;

In this example, error number -20343 is raised if the value of nBalance isnít greater than 100, yielding a message that looks like this:

ORA-20343: The balance is too low.

 

         
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