Bulk Collect and FORALL: Correctness, Sparse Collections, and Knowing When Not To - Part II
In Part 1 I covered four performance patterns I check when an existing BULK COLLECT or FORALL procedure starts to underperform at scale: row-level triggers, missing LIMIT clauses, the optimiser's silent cursor-loop rewrite, and sequence cache contention.
This part is about correctness. The procedure runs fast, but does it do the right thing when something goes wrong? Three of the four tips below are about handling failure modes I see misused or skipped in production code. The fourth is the contrarian one: knowing when bulk binding is the wrong choice in the first place.
5. SAVE EXCEPTIONS is not optional in a real pipeline
The default behaviour of FORALL is all-or-nothing. The moment one of your inserts or updates hits a constraint violation, the FORALL terminates and any rows that succeeded before the failure are rolled back as part of the surrounding transaction. In a 25,000-row file upload, one bad row stops the whole batch.
That is almost never what you want. The standard fix is SAVE EXCEPTIONS, which the Oracle PL/SQL Language Reference describes as follows: when a DML statement fails, PL/SQL does not raise an exception immediately. It saves the failure information and continues. After the FORALL completes, PL/SQL raises a single exception, ORA-24381, which you catch and inspect.
The shape of a SAVE EXCEPTIONS handler looks like this:
declare
e_bulk_failures EXCEPTION;
PRAGMA EXCEPTION_INIT(e_bulk_failures, -24381);
type t_values is table of stg_value%ROWTYPE;
l_values t_values;
begin
-- populate l_values from the table or cursor
...
forall i in 1 .. l_values.COUNT SAVE EXCEPTIONS
insert into my_table values l_values(i);
exception
when e_bulk_failures then
for j in 1 .. SQL%BULK_EXCEPTIONS.COUNT loop
insert into load_errors
( load_id
, row_number
, error_code
, error_message
, source_data )
values
( g_load_id
, SQL%BULK_EXCEPTIONS(j).ERROR_INDEX
, SQL%BULK_EXCEPTIONS(j).ERROR_CODE
, SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)
, l_values(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).value_code );
end loop;
end;
A few things in here that catch people out:
The negative sign on ERROR_CODE.
SQL%BULK_EXCEPTIONS(j).ERROR_CODE is stored as an unsigned positive number. To look up the message text with SQLERRM, you have to negate it: SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE). Steven Feuerstein flags this explicitly in Feuertip #15. Forgetting the minus sign is the most common mistake that can be seen in existing error handlers, and it produces messages like "ORA-00000: normal, successful completion" which is unhelpful.
The error message itself is not stored.
SQL%BULK_EXCEPTIONS only captures the error code, not the full message text. If your trigger does RAISE_APPLICATION_ERROR(-20000, 'APP-12345'), you will get ORA-20000 back but the application-specific message text is gone. If your error logging depends on that text, you have to look it up another way, usually via the source data plus a separate validation pass.
ERROR_INDEX is the iteration number, not always the collection index. This one is the real trap. I cover it in the next tip.
The pattern above writes failed rows to an error table so the load can be triaged afterwards. That is the minimum I would accept in any production upload pipeline. Anything less and you are losing data silently or stopping the whole batch for a single bad row.
6. The ERROR_INDEX trap with sparse collections, INDICES OF, and VALUES OF
This is the gotcha that produces the most confusing bug reports in bulk pipelines.
SQL%BULK_EXCEPTIONS(j).ERROR_INDEX gives you the iteration number of the failed statement within the FORALL, not necessarily the index value of the failing element in your collection. When the FORALL bounds clause is the simple 1..coll.COUNT and the collection is dense, the two are the same. When the collection is sparse, or when you use INDICES OF or VALUES OF, they are not.
The Oracle docs are explicit on this point. From the Bulk SQL chapter:
“If the FORALL statement bounds clause references a sparse collection, then to find the collection element that caused a DML statement to fail, you must step through the elements one by one until you find the element whose index is SQL%BULK_EXCEPTIONS(i).ERROR_INDEX”
In practice this means: do not write l_values(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX) when your collection might be sparse. You will either get the wrong element back or hit ORA-01403: no data found if that index has been deleted.
When does a collection become sparse? Two common ways:
You called .DELETE(n) on it.
Filtering out invalid rows by deleting them from the collection (rather than building a new collection) leaves gaps.
You used INDICES OF to drive the FORALL.
INDICES OF is the documented way to handle sparse collections. It iterates over only the populated indices:
forall i in indices of l_values save exceptions insert into my_table values l_values(i);
This works fine for the DML. The trap is in the exception handler. If iteration #3 of the FORALL fails, SQL%BULK_EXCEPTIONS(j).ERROR_INDEX will be 3. But the third populated index of your collection might be position 7 (if positions 1, 2, 4, 5, 6 are all empty, then 1 is the first, 3 the second, 7 the third). Referencing l_values(3) will fail.
VALUES OF has the same problem, with an extra layer of indirection. The bounds clause refers to a collection of pointers into another collection:
forall i in values of l_indexes save exceptions insert into my_table VALUES l_values(i);
ERROR_INDEX here gives you an iteration number, and you need to look up l_indexes(ERROR_INDEX) to get the position in l_values. Easy to get wrong, and the failure mode is silent: the wrong row gets logged as the failure.
When I work on existing code that uses sparse collections with SAVE EXCEPTIONS, I usually rewrite the exception handler to maintain a parallel array that maps FORALL iterations back to source rows. It is uglier than the naive l_values(ERROR_INDEX) pattern, but it produces correct error logs. The alternative is to keep collections dense (build a new collection rather than deleting from the original), which makes the simple pattern work but uses more memory.
7. Record collections vs scalar arrays
When I review existing PL/SQL, I see both patterns in use and they are rarely chosen deliberately. The choice matters for both readability and memory.
A record collection holds one collection of rows, where each element is a record. This is the natural pattern for "BULK COLLECT a query into a thing I can FORALL back out":
type t_assets is table of assets%ROWTYPE; l_assets t_assets; select * bulk collect into l_assets from stg_assets where batch_id = p_batch_id; forall i in 1 .. l_assets.COUNT insert into assets values l_assets(i);
A set of scalar collections holds one collection per column:
type t_ids is table of assets.asset_id%TYPE; type t_codes is table of assets.asset_code%TYPE; type t_descs is table of assets.description%TYPE; l_ids t_ids; l_codes t_codes; l_descs t_descs; select asset_id, asset_code, description bulk collect into l_ids, l_codes, l_descs from stg_assets where batch_id = p_batch_id; forall i in 1 .. l_ids.COUNT insert into assets (asset_id, asset_code, description) values (l_ids(i), l_codes(i), l_descs(i));
The scalar pattern is older. Before you could not reference individual columns of a record collection inside a FORALL, so if you needed to access individual columns (say, to insert into a different table or transform a value), you had to use scalar collections. That restriction was lifted in 11g, but a lot of code still follows the older pattern out of habit.
My rule of thumb:
Use record collections when you are inserting whole rows into the same shape as the source. The code is shorter and the intent is obvious.
Use scalar collections when you only need a subset of columns, or when the columns get inserted into different tables, or when you are transforming values and the transformation doesn't map cleanly to a single record type.
The memory cost is similar in either case. What differs is readability and the verbosity of declaration. Record collections win on both counts when the shape matches; scalar collections win when it doesn't.
One thing to flag: a record collection of table%ROWTYPE carries every column in the table, even columns you do not need. If you only need three columns out of forty, define a smaller record type explicitly rather than using %ROWTYPE. The memory difference at 25,000 rows is real.
8. Sometimes the answer is "don't bulk-ify it"
The internet is full of advice that says "always use BULK COLLECT and FORALL." That is not how I work. The overhead of declaring collections, managing memory, and writing the exception handler is real, and for small or simple operations it is not worth it.
Cases where I leave a row-by-row cursor FOR loop alone:
The result set is genuinely small and bounded.
A lookup that returns a handful of configuration rows. A loop over a small set of distinct values. The optimiser will already use BULK COLLECT LIMIT 100 under the hood for the fetching (covered in Part 1), and the body is fast enough that the rewrite buys you nothing.
The DML is one row at a time anyway.
Some procedures process one logical record at a time, where each iteration involves a chain of dependent operations: insert a header, then insert N detail rows, then call a posting routine. The "set" you are operating on is conceptual rather than physical. Trying to FORALL this just splits the logic across pieces of code that have to coordinate, and the gains are marginal because the bottleneck is somewhere else entirely.
You need fine-grained error handling.
SAVE EXCEPTIONS gives you batch-level error capture, but it has limits. If each row needs its own savepoint, or each row triggers a separate downstream action that may or may not run depending on success, a row-by-row loop with per-iteration exception handlers is sometimes the clearer choice. You give up speed for control.
The procedure is called once a day on a small input.
A nightly job that processes a few hundred rows does not need bulk binding. The five seconds you might save are not worth the maintenance cost of more complex code.
The flip side, the cases where I always reach for BULK COLLECT and FORALL:
Any DML inside a loop over a result set of more than a few hundred rows.
File upload pipelines.
Any data transformation between staging and production tables.
Bulk validation passes where the DML is gated on a check.
The decision is not "is bulk faster?" but "is bulk worth the added complexity for this specific case?" For high-volume code, yes, every time. For occasional low-volume code, often no.
Summary
The eight tips across these two posts are not the full picture. The Oracle PL/SQL Language Reference has a whole chapter on bulk SQL and bulk binding that goes deeper on the syntax variants, and Steven Feuerstein's Feuertips series is the place I recommend if you want to go further.
What I hope these two posts give you is the shape of a review checklist for existing bulk code:
Are row-level triggers on the target table cancelling out the bulk advantage?
Is the BULK COLLECT chunked with a LIMIT?
Has the cursor FOR loop been left alone because the optimiser already handles it?
Is sequence cache contention silently throttling the inserts?
Is there a SAVE EXCEPTIONS handler, and does it use the negative-sign trick correctly?
If the collection is sparse, is the exception handler doing the right index lookup?
Is the record-vs-scalar choice deliberate?
Should this code have been bulk-bound at all?
When I am brought in to look at why a procedure has stopped scaling, these are the questions I ask. They catch most of what goes wrong.
