totn Oracle / PLSQL

Oracle / PLSQL: 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
DUP_VAL_ON_INDEX ORA-00001
TIMEOUT_ON_RESOURCE ORA-00051
TRANSACTION_BACKED_OUT ORA-00061
INVALID_CURSOR ORA-01001
NOT_LOGGED_ON ORA-01012
LOGIN_DENIED ORA-01017
NO_DATA_FOUND ORA-01403
TOO_MANY_ROWS ORA-01422
ZERO_DIVIDE ORA-01476
INVALID_NUMBER ORA-01722
STORAGE_ERROR ORA-06500
PROGRAM_ERROR ORA-06501
VALUE_ERROR ORA-06502
CURSOR_ALREADY_OPEN ORA-06511

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.