How do you handle PL/SQL errors that are not predefined?
Predefined exceptions are the following:
You could use a WHEN OTHERS exception handler that looks at the SQLCODE. But you're generally better off defining an exception that you can handle. That is going to lead to cleaner code, it lets you map your exception name to a number just once, and makes user-defined exceptions look just like predefined exceptions.
declare
column_already_indexed exception;
pragma exception_init( column_already_indexed, -1408 );
begin
call_procedure;
exception
when column_already_indexed
then
null;
-- Do something with the exception
end;
You can handle errors outside of the predefined with the following:
BEGIN
buggyprocedure; --Call to a procedure that can throw exceptions
EXCEPTION
WHEN NO_DATA_FOUND THEN --Catch predefined exception
NULL; --Ignore no_data_found
WHEN OTHERS THEN --Catch all other exceptions
IF SQLCODE = -1408 THEN --Catch error -1408
NULL; --Ignore this exception
ELSEIF SQLCODE = -955 THEN --Catch error -955
anotherprocedure; --Call a different procedure
ELSE
RAISE; --Re-raise exception that we are not handling
END IF;
END;
You basically catch all other exceptions, then use if and else statements to check if the SQLCODE returned is one you are expecting and want to handle.