Continue PL/SQL execution beyond exceptions.
The procedure must continues past any exceptions and completes as many actions as possible. What are my options in PL/SQL for doing this?
In Oracle Database 10g and higher, PL/SQL offers three options for “continuing past an exception”, which really means avoiding termination of the execution of the current block.
- The nested block. You enclose the lines of code that may raise the exception inside a BEGIN END nested block. Then add an exception section so that the error is trapped and handled. Then keep on going.
- The FORALL SAVE EXCEPTIONS clause. Add SAVE EXCEPTIONS to a FORALL statement, and Oracle Database will save any exceptions that are raised during the execution of bulk insert, update, delete, or merge statements. FORALL SAVE EXCEPTIONS suppresses exceptions at the statement level, so if a statement’s change of one row raises an error, changes to other rows already completed by that statement are also rolled back. When the FORALL statement is completed, Oracle Database will then raise the ORA-21438 error if at least one exception was encountered.
- Data manipulation language (DML) error logging. Use the DBMS_ERRLOG package and LOG ERRORS with any insert, update, delete, or merge statements (within or independent of FORALL) to suppress exceptions at the row level. If a statement’s change of one row raises an error, changes to other rows already made by that statement will not be rolled back and Oracle Database will continue to change any remaining rows identified by that statement. DBMS_ERRLOG and LOG ERRORS will then write error information out to an error log table.