In Oracle APEX bulk operations, the performance issue is often not the page itself.
Sometimes it is in the PL/SQL behind the page: a file upload process, a staging table load, a validation routine, an integration job, or a batch update that needs to move more than a few rows at a time.
For those cases, Oracle developers often reach for BULK COLLECT and FORALL. And often, that is the right place to start. Reducing row-by-row context switches between PL/SQL and SQL can make a meaningful difference.
But bulk binding still has to be reviewed carefully.
Magaly Iraheta, Senior Oracle APEX Developer at Traust and Oracle ACE Associate, recently published two posts on her blog — The Coder Gal — that look at BULK COLLECT and FORALL from two practical angles: performance and correctness.
The posts are written for developers who already understand the basic syntax but want a better way to evaluate bulk code in real applications. Rather than walking through another basic example of bulk syntax, Magaly focuses on the review questions that come up after the first version of the code has already been written.
- Is the process still doing row-by-row work somewhere else?
- Is the collection size controlled?
- Are exceptions being handled in a way that preserves the relationship between the failed operation and the source data?
When Bulk Code Still Underperforms
In the first post, Magaly covers four things to check when Oracle APEX bulk operations are still slower than expected.
One of the first is row-level triggers. FORALL can reduce context switches, but it does not prevent a row-level trigger from firing once for every affected row. If the trigger is doing expensive work, the bulk operation may still be slow.
She also covers the importance of using LIMIT with BULK COLLECT. Code that works well against a small test set can become a problem when the same process runs against production-sized data. Fetching in chunks helps keep memory usage under control.
The post also looks at cases where a cursor FOR loop may already be optimized by Oracle, as well as sequence cache contention during high-concurrency inserts. Both are good reminders that the PL/SQL syntax is only one part of the performance picture.
Read part 1: Bulk Collect and FORALL: Four Things to Check When Bulk Code Underperforms
Correctness, Sparse Collections, and Error Handling
In the second post, Magaly focuses on correctness in Oracle APEX bulk operations, picking up where performance tuning usually leaves off. Once the code is using bulk operations, the next review is about how the process behaves when the data is incomplete, inconsistent, or only partially successful.
That includes SAVE EXCEPTIONS, SQL%BULK_EXCEPTIONS, sparse collections, and how to map errors back to the correct source record. These are the details that become especially important when a process needs to continue after some rows fail, while still producing useful error information.
She also covers the difference between record collections and scalar arrays, including when each approach can make the code easier to manage.
One of the more useful parts of the post is the discussion of when not to use bulk binding. For small, bounded result sets, highly conditional row-by-row logic, or low-volume jobs, the added complexity may not be worth it.
Read part 2: Bulk Collect and FORALL: Correctness, Sparse Collections, and Knowing When Not To
Further Reading for APEX and PL/SQL Developers
If you work on Oracle APEX bulk operations, PL/SQL bulk processing, file uploads, staging-table transformations, or high-volume validation routines, Magaly’s posts are a useful reference. They do not just show how to use BULK COLLECT and FORALL. They show what to check after the code already exists.
For more posts from Traust’s Oracle APEX developers, follow along with our latest technical articles and conference updates.
