Oracle/PLSQL: Named System Exceptions
Learn how to use Named System Exceptions in Oracle/PLSQL with syntax and examples.
What is a named system exception?
Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.
Oracle has a standard set of exceptions already named as follows:
|Oracle Exception Name||Oracle Error||Explanation|
|DUP_VAL_ON_INDEX||ORA-00001||You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.|
|TIMEOUT_ON_RESOURCE||ORA-00051||You were waiting for a resource and you timed out.|
|TRANSACTION_BACKED_OUT||ORA-00061||The remote portion of a transaction has rolled back.|
|INVALID_CURSOR||ORA-01001||You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor.|
|NOT_LOGGED_ON||ORA-01012||You tried to execute a call to Oracle before logging in.|
|LOGIN_DENIED||ORA-01017||You tried to log into Oracle with an invalid username/password combination.|
|NO_DATA_FOUND||ORA-01403||You tried one of the following:
|TOO_MANY_ROWS||ORA-01422||You tried to execute a SELECT INTO statement and more than one row was returned.|
|ZERO_DIVIDE||ORA-01476||You tried to divide a number by zero.|
|INVALID_NUMBER||ORA-01722||You tried to execute a SQL statement that tried to convert a string to a number, but it was unsuccessful.|
|STORAGE_ERROR||ORA-06500||You ran out of memory or memory was corrupted.|
|PROGRAM_ERROR||ORA-06501||This is a generic "Contact Oracle support" message because an internal problem was encountered.|
|VALUE_ERROR||ORA-06502||You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.|
|CURSOR_ALREADY_OPEN||ORA-06511||You tried to open a cursor that is already open.|
We will take a look at the syntax for Named System Exceptions in both procedures and functions.
Syntax for Procedures
The syntax for the Named System Exception in a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] IS [declaration_section] BEGIN executable_section EXCEPTION WHEN exception_name1 THEN [statements] WHEN exception_name2 THEN [statements] WHEN exception_name_n THEN [statements] WHEN OTHERS THEN [statements] END [procedure_name];
Syntax for Functions
The syntax for the Named System Exception in a function is:
CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ] RETURN return_datatype IS | AS [declaration_section] BEGIN executable_section EXCEPTION WHEN exception_name1 THEN [statements] WHEN exception_name2 THEN [statements] WHEN exception_name_n THEN [statements] WHEN OTHERS THEN [statements] END [function_name];
Here is an example of a procedure that uses a Named System Exception:
CREATE OR REPLACE PROCEDURE add_new_supplier (supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2) IS BEGIN INSERT INTO suppliers (supplier_id, supplier_name ) VALUES ( supplier_id_in, supplier_name_in ); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN raise_application_error (-20001,'You have tried to insert a duplicate supplier_id.'); WHEN OTHERS THEN raise_application_error (-20002,'An error has occurred inserting a supplier.'); END;
In this example, we are trapping the Named System Exception called DUP_VAL_ON_INDEX. We are also using the WHEN OTHERS clause to trap all remaining exceptions.