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: 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.

  v_number NUMBER;
    INTO v_number
    FROM dual
    WHERE 1 = 2;
  WHEN no_data_found
    DBMS_OUTPUT.PUT_LINE( 'No Data Found');

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 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:

  v_number NUMBER;
  IF 1 != 2 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Numbers do not match');
    IF SQLCODE = -20001
    END IF;

INFO:  EDB--20001: Numbers do not match


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