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

 

 

   
 

EnterpriseDB: Named Exceptions
Oracle Tips by Burleson
 

A named exception is one of the pre-defined exceptions that come supplied in EnterpriseDB.  When referring to a named exception, you use a textual name instead of an error code.

DECLARE
  v_number NUMBER;
BEGIN
  SELECT 1
    INTO v_number
    FROM dual
    WHERE 1 = 2;
EXCEPTION
  WHEN no_data_found
  THEN
    DBMS_OUTPUT.PUT_LINE( 'No Data Found');
END;

INFO:  No Data Found

Chapter 8 contains a list of all named, Oracle compatible exceptions and the associated code.  Exceptions that you will use frequently, and that are fairly self-explanatory are: 

* no_data_found – A select into statement returned no rows

* too_many_rows – A select into statement returned more than one row

* dup_val_on_index – An insert or update tried to create a non-unique row on a column or key identified as unique (can be multi-column)

* zero_divide – A divide by zero exception

* cursor_already_open – An open cursor command was issued against an already open cursor

* invalid_cursor – A cursor operation was attempted against a cursor that is not open

SQLCODE and SQLERRM

SQLCODE and SQLERRM are variables populated by the database.  Upon an exception condition, the database instantiates the two variables.  SQLCODE stores the database error code and SQLERRM stores a text description of the error.

You cannot manually assign values to either SQLCODE or SQLERRM.  You can use RAISE_APPLICATION_ERROR to assign values to these variables.

User Defined Exceptions

User defined exceptions are defined though the built-in procedure, RAISE_APPLICATION_ERROR.  This procedure has the syntax:

RAISE_APPLICATION_ERROR(<numeric code>, <text message>);

When you call raise_application_error, program control is passed to the nearest exception handler.  The numeric code can be any number but best practice recommends that you use a negative number to distinguish application codes from built-in codes.

Raise_application_error populates the SQLCODE with the numeric code and the SQLERRM with the text message.  You cannot test for user defined exceptions by name.  You test for them in the OTHERS branch via the SQLCODE:

DECLARE
  v_number NUMBER;
BEGIN
  IF 1 != 2 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Numbers do not match');
  END IF;
EXCEPTION
  WHEN OTHERS
  THEN
    IF SQLCODE = -20001
    THEN
      DBMS_OUTPUT.PUT_LINE( SQLERRM);
    END IF;
END;

INFO:  EDB--20001: Numbers do not match

RAISE

In Oracle's PL/SQL, if you want to re-raise the same exception, you can use the RAISE keyword.  When written by itself in an exception handler, RAISE will re-raise the current exception.  You would use a RAISE if you need to process rollbacks, commits or other executable but you want to propagate the exception to the calling routine.

The RAISE keyword, to re-raise an exception, is not currently supported in SPL.  You may use raise to raise a named exception such as no_data_found.  The syntax is the same as PL/SQL, raise <exception name>.

             

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