Named System Exceptions in Oracle PLSQL

Oracle PLSQL exception is the condition that terminates the execution of plsql block.

Named System PLSQL Exceptions:-

The named system exceptions are the plsql exception that are encountered frequently and hence have been named for ease of use and that’s why they are called as named system exception.

The named plsql exceptions are named in the standard package in plsql and hence the developer does not need to define the plsql exception.

Below is the list of named system plsql exception:

Oracle PLSQL Exception Name

Oracle Error #

Oralce PLSQL Exception Explanation

DUP_VAL_ON_INDEX ORA-00001 We tried to execute INSERT or UPDATE statement which tried to create a duplicate value in a field restricted by unique index.
TIMEOUT_ON_RESOURCE ORA-00051 We were waiting for a resource and we timed out.
TRANSACTION_BACKED_OUT ORA-00061 The remote portion of a transaction has rolled back.
INVALID_CURSOR ORA-01001 We tried to reference a cursor that does not exist. This may have happened because we’ve executed a FETCH cursor or CLOSE cursor before OPENing the cursor.
NOT_LOGGED_ON ORA-01012 We tried to execute a call to Oracle before logging in.
LOGIN_DENIED ORA-01017 We tried to log into Oracle with an invalid username/password combination.
NO_DATA_FOUND ORA-01403 We tried one of the following:

  1. executed a SELECT INTO statement and no rows were returned.
  2. referenced an uninitialized row in a table.
  3. read past the end of file with the UTL_FILE package.
TOO_MANY_ROWS ORA-01422 We tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE ORA-01476 We tried to divide a number by zero.
INVALID_NUMBER ORA-01722 We tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
STORAGE_ERROR ORA-06500 We 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 We 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 We tried to open a cursor that is already open.

The plsql syntax for the Named System Exception within a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
–executable_statements
EXCEPTION
WHEN exception_name1 THEN

[exception1_handling_statements]
WHEN exception_name2 THEN
[exception2_handling_statements]
WHEN exception_name_n THEN
[exceptionN_handling_statements]
WHEN OTHERS THEN
[generic_handling_statements]
END [procedure_name];


The plsql 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_statements
EXCEPTION
WHEN exception_name1 THEN

[exception1_handling_statements]
WHEN exception_name2 THEN
[exception2_handling_statements]
WHEN exception_name_n THEN
[exceptionN_handling_statements]
WHEN OTHERS THEN
[generic_handling_statements]
END [function_name];


Example of plsql exception (named system exception) handling in a procedure is:

CREATE OR REPLACE PROCEDURE add_new_employee
  (employee_id_in IN NUMBER, employee_name_in IN VARCHAR2)
IS

BEGIN
  INSERT INTO employee (employee_id, employee_name )
  VALUES (  employee_id_in, employee_name_in );

EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'Duplicate employee_id');

  WHEN OTHERS THEN
raise_application_error (-20002,'An error occurred.');

END;

Tagged , , , , , . Bookmark the permalink.