Named System Exceptions

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: 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]; 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. Download Hear Click Hear