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
replace(upper(trim(text)),'PRAGMA EXCEPTION_INIT(', ''),
'''', '') as e
where name = 'STANDARD'
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.
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.