Named System Exceptions
This Oracle tutorial explains how to use Named System Exceptions in Oracle/PLSQL with syntax and examples.
What is a named system exception in Oracle?
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. |
Syntax
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];
Example
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.