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: executed a SELECT INTO statement and no rows were returned. referenced an uninitialized row in a table. 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;