Identifying System Generated Exceptions

Contact Us or call 1-877-932-8228
Identifying System Generated Exceptions

Identifying System Generated Exceptions

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.

select regexp_substr(e, '[^,]+', 1, 1) as error_name, regexp_substr(e, '[^,]+', 1, 2) as error_code from (select replace( replace( replace(upper(trim(text)),'PRAGMA EXCEPTION_INIT(', ''), ')', ''), '''', '') as e from all_source where name = 'STANDARD' and type='PACKAGE' and text like '%EXCEPTION_INIT%') order by error_name;

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.

Predefined exceptions in the standard package

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.

Code Sample:

  null_not_allowed EXCEPTION;
  PRAGMA EXCEPTION_INIT(null_not_allowed, -1400);
  insert into employees
  (employee_id, first_name, last_name, email, job_id, hire_date)
  (2000, 'Larry', null, 'lellison', 'AD_PRES', current_date);

  WHEN null_not_allowed THEN 

    DBMS_OUTPUT.PUT('You attempted to enter a null value');
    DBMS_OUTPUT.PUT_LINE(' in a required column.');

Code Explanation

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.