Oracle PL/SQL provides an
exception clause to allow you to stop execution of your program
whenever Oracle returns a specific ORA-xxxx error.
Oracle provides the generic when others clause for trapping
all non-zero error codes.
However, there are cases when you expect an Oracle error to be
thrown, such as a PL/SQL code that creates a table, and ignores the
error if the table already exists. (The ORA-00955 error below).
Remember, PL/SQL is separate from the Oracle database, and whenever
you call Oracle, you want to check the return code to ensure that
Oracle has done what you wanted. See these important notes on
PL/SQL error handling. Also note that you can handle
PL/SQL exceptions when using bulk operators (forall and bulk
collect)
It's easy to use the built-in values with the
PL/SQL exceptions clause, but this list does not cover all
1,000+ possible Oracle errors.
Let's look at how to define a custom exception for an Oracle ORA-
error code.
Defining a custom Oracle error exception in PL/SQL
Oracle has over
1,000
distinct error codes, and this is how to translate an ORA-0nnn
to a SQLCODE.
For example, let's assume that we want to ignore the ORA-00955
error, where a table already exists. The SQLCODE is the same as the
Oracle error code with the leading ORA-000's stripped off:
Oracle error SQLCODE
ORA-01555
-1555
ORA-00600 -600
ORA-03113 -3113
ORA-12560
-12560
ORA-03113 -3113
etc . . .
Below we trap an ORA-00955 error which translates into a -955
SQLCODE. We define a custom pragma exception TableExists) and then
check this Boolean in the EXCEPTION clause:
DECLARE
TableExists EXCEPTION;
pragma
exception_init(TableExists,-955);
sql_stmt varchar2(50) := 'create table
temp (col1 number)';
BEGIN
execute immediate sql_stmt;
/*+ Ignore ORA-955 errors
when the table already exists) */
EXCEPTION when TableExists then NULL;
END;
