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




EnterpriseDB: Exception Handling
Oracle Tips by Burleson


SPL has very robust exception handling.  An exception handler takes care of "expected" errors and can be built to intelligently handle unexpected exceptions. 

As I have discussed above, the exception area is one of the areas in a SPL block.  The syntax for the EXCEPTION area is:

  WHEN <exception> THEN
    <executable statements>;
  [WHEN <exception> THEN
    <executable statements>;]
   <executable statements>;

The EXCEPTION keyword identifies the exception area.  The EXCEPTION keyword is followed by one or more WHEN clauses.  The WHEN clause identifies the named exception that you are handling, i.e. NO_DATA_FOUND, or the OTHERS clause.  The OTHERS will fire for any exceptions.

The WHEN clauses fire in the order written and firing will stop at the first WHEN clause that matches the current exception.  That means that if you are going to include an OTHERS handler, it will always be the final WHEN clause.

The easiest way to think about exceptions is to think of them as unfavorable interruptions in program flow control.  The exceptions may or may not be an expected change in your program flow, but an exception is always (mostly) unfavorable.  That's the easiest way but, of course, every rule has an exception.  It would not be an error to say that exceptions are no exception to that rule. 

You will certainly use exceptions to handle errors in your program.  No data when you expect it, too many rows returned from a select statement, user defined errors, etc.  An exception may be completely expected though.  

In certain cases, not finding data is the expected result of a query and you have the choice of using an explicit or implicit cursor.  Implicit cursors are easier to maintain and the way you would "check" for no rows would be to use a no data found exception handler. 

I think it's important for me to say here that I have seen a lot of very good code and a lot of very bad code.  SPL is a very powerful language.  Exceptions are a very powerful feature of that language.  DO NOT use exceptions in place of GOTO.  If you do, you will create an unmaintainable mess. 

* Do NOT use exceptions as a cheap GOTO.  GOTO is bad for a reason.  Exceptions used that way are just as bad. 

Exceptions are there to allow you to gracefully handle exception conditions in your logic.  Use them that way.  A good rule of thumb when creating your exception handler is to consider if you could replace the exception with a GOTO and a label.  If you can, you are probably using the exception handler inappropriately.

Repeat after me:  exceptions are not flow control.  Exceptions should not be used for flow control.  They are to handle exceptions (and may change the flow) but they are not intended to manage flow.

There are two kinds of exceptions you will use in SPL: Named Exceptions and User Defined Exceptions.  Named exceptions are part of the language and you cannot change them.  User defined exceptions are created with the built-in procedure, RAISE_APPLICATION_ERROR.


This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


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