Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle
Although this topic involves a much larger discussion of how to optimize database DML operations for large record sets, this approach is applicable for variable magnitudes of record volumes and even has a nice feature set, which includes a DML option called SAVE EXCEPTIONS, which enables a database operation to SKIP and continue past individual DML transactions that have encountered EXCEPTIONS.
I have adapted a script and added some additional explanatory notation to show how this works. If you would like some additional reading from the source I used, see the following link for a discussion on Oracle DML Bulk Binds and Record Processing.
The Sample Table: exception_test
Use the DDL Below to create the test table used to store the data from our procedure's Bulk Binding DML commands:
CREATE TABLE "EXCEPTION_TEST"
( "ID" NUMBER(15,0) NOT NULL ENABLE,
CONSTRAINT "EXCEPTION_TEST_PK" PRIMARY KEY ("ID") ENABLE
)
/
The Primary Key is the single column. It has an assigned NOT NULL constraint which will be the property used later for generating several exceptions.
Stored Procedure: proc_exam_save_exceptions
create or replace PROCEDURE proc_exam_save_exceptions IS
-- Declare and Instantiate Data Types
TYPE t_tab IS TABLE OF exception_test%ROWTYPE;
l_tab t_tab := t_tab();
l_error_count NUMBER;
ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
-- Fill the collection.
FOR i IN 1 .. 2000 LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
END LOOP;
-- Cause a failure or two.
l_tab(50).id := NULL;
l_tab(51).id := NULL;
l_tab(1250).id := NULL;
l_tab(1252).id := NULL;
EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';
-- Perform a bulk operation.
BEGIN
FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
INSERT INTO exception_test
VALUES l_tab(i);
EXCEPTION
WHEN ex_dml_errors THEN
l_error_count := SQL%BULK_EXCEPTIONS.count;
DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
FOR i IN 1 .. l_error_count LOOP
DBMS_OUTPUT.put_line('Error: ' || i ||
' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
END;
The first loop instantiates a collection type variable (nested table) and initializes it with a non-null value. Note the block of this procedure:
-- Cause a failure or two.
l_tab(50).id := NULL;
l_tab(51).id := NULL;
l_tab(1250).id := NULL;
l_tab(1252).id := NULL;
Which changes index positions 50, 51, 1250 and 1252 with a NULL value to force a DML error against the table constraint of the ID column on table exception_test. Compiling this procedure and executing it from the command line reveals in the DBMS OUT display a feedback message identifying the DML operations that failed by an internal count of each loop iteration...
Testing the Stored Procedure
Call the procedure to iterate through the defined DML loops and verify the BULK EXCEPTION HANDLING feature at work.
-- Command to Call Procedure
begin
proc_exam_save_exceptions();
end;
The following is the output of this procedure's execution:
Number of failures: 4
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()
Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()
Error: 3 Array Index: 1250 Message: ORA-01400: cannot insert NULL into ()
Error: 4 Array Index: 1252 Message: ORA-01400: cannot insert NULL into ()
Statement processed.
0.05 seconds
An additional query of the target table shows that DML errors introduced in the middle of the loop iterations did not interrupt the completion of the other loops and their assigned DML operation.
Additional Notes and Discussion
Here are some parting notes to keep in mind when using the SAVE EXCEPTIONS Bulk DML option:
The meta information in the error_index value of the resulting collection: SQL%BULK_EXCEPTIONS is based on the count of loop iterations from the initial cursor query of the data.
i.e., You will still need to correlate in some way that the error in loop iteration #51 (error_index = 51) lines up with whatever identifying key exists in your actual DML target tables. I recommend at least using ORDER BY clauses carefully and consistently in your DML cursors so that your loop iterations are consistently matched with the same index/key values.
There are some additional extensions to the functionality of the SAVE EXCEPTIONS and BULK DML handling alternatives that may prove to serve additional utility above traditional approaches to large volume DML operations. Among them includes: error threshold limitations (i.e., quit when a pre-defined number of DML exceptions are handled) and Bulk Processing loop size definitions.
