On Avoiding Termination
Has one found a procedure that continues to apply a complex set of rules to a large volume of data in a set of tables even if an error appears in the update? In the past, if an error occurred in the up-date process, the procedure would terminate execution. This paper looks for a solution that would successfully overcome past exceptions and is able to complete as many updates as possible. In order to look for a solution for avoiding termination of the execution of the current block, Oracle Database 10g and higher, PL/SQL offers three options: the nested block; the FORALL Save Exceptions clause and; Data Manipulation Language (DML) error logging. The weaknesses and strengths of each of the approaches are being studied to. After studying three of the approaches at depth, it was concluded that all three approaches had qualities that would enable the user to continue past exceptions. As the nested block approach offered the advantage of keeping the codes concise and simple and is easy to understand and maintain; Save Exceptions records all error codes in the SQL% BULK_EXCEPTIONS, pseudo collection of records which are automatically erased by Oracle Database. On the other hand, DML error logging records error information in a persistent database table, that must be manually cleaned up to ensure that errors from subsequent statements are not mixed up with earlier errors. In the end, it was concluded that DML error logging with the DBMS_ERRLOG package and LOG ERRORS is the optimal solution.