Bulk Operations: BULK COLLECT and FORALL

BULK COLLECT INTO fetches multiple rows from a query directly into a PL/SQL collection (associative array, nested table, or VARRAY) in a single fetch, rather than looping with FETCH ... INTO row by row. FORALL does the inverse for DML: it takes a collection and issues the INSERT/UPDATE/DELETE for every element in a single batch, still executing individual DML statements under the hood but eliminating the context-switch overhead of a PL/SQL loop wrapping each one.

In EBS customizations — like a custom program processing tens of thousands of interface rows or applying corrections across large transaction sets — using BULK COLLECT with the LIMIT clause (to avoid consuming excessive PGA memory) combined with FORALL is considered a baseline professional practice. Oracle's own interface import programs use these techniques internally for this reason.

Interviewers commonly ask candidates to rewrite a naive row-by-row cursor loop using BULK COLLECT/FORALL, and to explain the SAVE EXCEPTIONS clause, which allows a FORALL statement to continue processing remaining rows even if some individual DML statements fail, collecting all errors into SQL%BULK_EXCEPTIONS for later review.