BULK COLLECT without a LIMIT is a memory leak waiting to happen

Every PL/SQL developer eventually learns that row-by-row is slow-by-slow. A cursor FOR loop does a context switch between the PL/SQL and SQL engines on every single iteration, and once you're processing more than a few thousand rows you feel it. The usual advice is: reach for BULK COLLECT. That advice is correct and incomplete.

Here is the version people write once they've heard "use bulk collect":

declare
  type t_rows is table of orders%rowtype;
  l_rows t_rows;
begin
  select * bulk collect into l_rows from orders;
 
  forall i in 1 .. l_rows.count
    update order_summary set ... ;
end;

This is fast. It is also a landmine. BULK COLLECT INTO with no bound loads the entire result set into PGA memory at once. On the developer's laptop orders has 40,000 rows and everything is wonderful. In production two years later it has 40 million, and the same block quietly allocates gigabytes of process memory, blows past PGA_AGGREGATE_LIMIT, and either throws ORA-04036 or drags the whole instance into swap. The code didn't change. The data did.

The clause everyone forgets

Bound the fetch with LIMIT and loop:

declare
  type t_rows is table of orders%rowtype;
  l_rows t_rows;
  cursor c is select * from orders;
begin
  open c;
  loop
    fetch c bulk collect into l_rows limit 1000;
    exit when l_rows.count = 0;
 
    forall i in 1 .. l_rows.count
      update order_summary set ... ;
 
    -- commit here if this is a batch job, not inside a transaction
  end loop;
  close c;
end;

Now memory is bounded by the batch size no matter how big the table gets. You keep almost all of the context-switch savings — the expensive part was never fetching 1,000 rows at a time versus 40 million, it was fetching one row at a time — and you've capped your blast radius.

A batch size of 100–1,000 captures the vast majority of the speedup. Going higher buys you very little throughput and costs you linearly more memory per session. If a hundred sessions run this concurrently, that "harmless" LIMIT 100000 is a hundred large allocations at the same time.

The bug hiding in the loop

There's a second trap, and it's subtle. The natural instinct is to write the exit check like this:

  loop
    fetch c bulk collect into l_rows limit 1000;
    exit when c%notfound;      -- WRONG
    ...
  end loop;

%NOTFOUND becomes true on the fetch that returns fewer rows than the limit — including a final partial batch that still has rows in it. Exit on %NOTFOUND and you silently skip the last 1–999 rows. Every time. Always test the collection count instead:

    exit when l_rows.count = 0;

And do your work first, then exit, or you'll drop that last partial batch a different way.

One more: FETCH ... BULK COLLECT replaces the collection on each iteration — it doesn't append. That's what you want here, but if you ever SELECT ... BULK COLLECT in a loop expecting accumulation, you'll only ever see the last batch.

When you don't need any of this

If the whole operation can be expressed as a single INSERT ... SELECT, MERGE, or UPDATE, do that instead and skip PL/SQL entirely. The fastest row-by-row loop is the one you deleted. BULK COLLECT with LIMIT is for when you genuinely need procedural logic per batch — calling a package, branching on values, writing to more than one target, catching per-row errors with SAVE EXCEPTIONS. When it's pure set logic, let the SQL engine do it in one statement.

The short version: BULK COLLECT fixes slow-by-slow, and LIMIT fixes the memory problem BULK COLLECT just created. You almost never want one without the other.