You might be wondering how to determine the name of a given exception to be handled. Predefined PL/SQL exceptions are listed in the Oracle Documentation. In addition, the following query shows the names of predefined exceptions declared globally in package STANDARD. It is not important to understand all of the details of this query. It is sufficient to understand that many Oracle exceptions that you will need to handle can be identified by name by consulting the documentation as needed.
The query lists lines from the STANDARD package as they are found in the all_source view. Only lines that include the substring EXCEPTION_INIT are included. The remaining parts of the query are function calls that strip out the error names and codes for final display.
You are not limited to handling the exceptions named above. You can use the pragma EXCEPTION_INIT to associate an exception name with any Oracle Database error code that does not have a predefined exception name.
DECLARE null_not_allowed EXCEPTION; PRAGMA EXCEPTION_INIT(null_not_allowed, -1400); BEGIN insert into employees (employee_id, first_name, last_name, email, job_id, hire_date) values (2000, 'Larry', null, 'lellison', 'AD_PRES', current_date); EXCEPTION WHEN null_not_allowed THEN DBMS_OUTPUT.PUT('You attempted to enter a null value'); DBMS_OUTPUT.PUT_LINE(' in a required column.'); END;
The previous query identified exceptions as named in the STANDARD package. This example shows that you can associate your own names with exceptions. The pragma EXCEPTION_INIT directive associates an exception name with the error raised when division by zero causes an exception to be raised. This shows that an internal exception can be referenced by name by a specific handler as long as you know the error code that does not have a predefined exception name.
Typically you do not use the pragma for Oracle error messages that already have a predefined exception name.