How to Use BULK COLLECT with LIMIT in PL/SQL

Read and change any number of rows in batches with BULK COLLECT LIMIT: 253 MB of PGA becomes 7 MB, with the right batch size and the exit condition that loses rows.

BULK COLLECT fetches many rows into a PL/SQL collection in one call, instead of one row per fetch.

It is the fastest way to read rows into PL/SQL, but a SELECT ... BULK COLLECT INTO with no limit loads the whole result set into session memory (PGA) at once. With a large table, that means hundreds of megabytes per session, and with many sessions it can run the instance out of memory (ORA-04036).

The LIMIT clause of FETCH fixes this. The program reads the rows in batches of a fixed size, processes each batch, and reuses the same collection, so memory stays small however many rows the cursor returns.

This guide measures the memory and time difference on 500,000 rows. It also shows how to choose the batch size, how to avoid the exit condition that loses the last batch, and how to combine LIMIT with FORALL to move rows in batches.

Syntax

open cursor_name;
loop
  fetch cursor_name bulk collect into collection_variable limit batch_size;
  exit when collection_variable.count = 0;

  process_the_batch;
end loop;
close cursor_name;

LIMIT is part of the FETCH statement, so it needs an explicit cursor or a cursor variable; a SELECT ... BULK COLLECT INTO statement has no LIMIT clause.

The batch size can be a literal, a constant, or a variable. Each FETCH replaces the contents of the collection with the next batch of up to batch_size rows. The collection_variable can be a collection of records (cursor%ROWTYPE) or one collection per selected column.

Create the Sample Tables

The examples use an orders table with 500,000 rows and an empty archive table with the same columns plus an archive date. Both have a primary key on ORDER_ID. A quarter of the orders are CLOSED.

Example:

create table bulk_orders (
  order_id     number        constraint bulk_orders_pk primary key,
  customer_id  number        not null,
  order_date   date          not null,
  amount       number(10,2)  not null,
  status       varchar2(10)  not null,
  note         varchar2(200)
);

create table bulk_orders_archive (
  order_id     number        constraint bulk_orders_archive_pk primary key,
  customer_id  number        not null,
  order_date   date          not null,
  amount       number(10,2)  not null,
  status       varchar2(10)  not null,
  note         varchar2(200),
  archived_on  date          default sysdate not null
);

insert into bulk_orders (order_id, customer_id, order_date, amount, status, note)
select level,
       mod(level * 7, 5000) + 1,
       date '2025-01-01' + mod(level, 600),
       mod(level * 13, 500) + 10,
       case mod(level, 4) when 0 then 'CLOSED' else 'OPEN' end,
       rpad('Order note ' || level, 150, '.')
from   dual
connect by level <= 500000;

commit;

select status, count(*) as orders
from   bulk_orders
group  by status
order  by status;

Output:

Table BULK_ORDERS created.

Table BULK_ORDERS_ARCHIVE created.

500,000 rows inserted.

Commit complete.

STATUS       ORDERS
_________ _________
CLOSED       125000
OPEN         375000

The measuring examples read the session statistic "session pga memory max" from V$MYSTAT and V$STATNAME. If your user cannot query these views, ask the DBA for SELECT on V_$MYSTAT and V_$STATNAME, or leave the memory lines out.

BULK COLLECT Without LIMIT

This block loads all 500,000 orders into one collection and reports the elapsed time and the highest PGA the session used:

Example:

declare
  type t_orders is table of bulk_orders%rowtype;
  l_orders t_orders;
  l_start  number := dbms_utility.get_time;

  function pga_max_mb return number is
    l_value number;
  begin
    select round(s.value / 1024 / 1024) into l_value
    from   v$mystat s join v$statname n on n.statistic# = s.statistic#
    where  n.name = 'session pga memory max';
    return l_value;
  end;
begin
  dbms_output.put_line('PGA before:  ' || pga_max_mb || ' MB');

  select * bulk collect into l_orders from bulk_orders;

  dbms_output.put_line('Rows loaded: ' || l_orders.count);
  dbms_output.put_line('Time:        ' || (dbms_utility.get_time - l_start) * 10 || ' ms');
  dbms_output.put_line('PGA peak:    ' || pga_max_mb || ' MB');
end;
/

Output:

PGA before:  6 MB
Rows loaded: 500000
Time:        200 ms
PGA peak:    253 MB

PL/SQL procedure successfully completed.

The session's PGA grew from 6 MB to 253 MB, because every row of the table sat in the collection at the same time. Ten sessions running this code at once need about 2.5 GB, and the memory grows with the table. On a busy system, the session fails with ORA-04036 when it reaches PGA_AGGREGATE_LIMIT.

The Same Rows with LIMIT

The same read with an explicit cursor and LIMIT 1000. Each FETCH returns the next 1,000 rows into the same collection:

Example:

declare
  cursor c_orders is select * from bulk_orders;
  type t_orders is table of c_orders%rowtype;
  l_orders  t_orders;
  l_rows    pls_integer := 0;
  l_batches pls_integer := 0;
  l_start   number := dbms_utility.get_time;

  function pga_max_mb return number is
    l_value number;
  begin
    select round(s.value / 1024 / 1024) into l_value
    from   v$mystat s join v$statname n on n.statistic# = s.statistic#
    where  n.name = 'session pga memory max';
    return l_value;
  end;
begin
  dbms_output.put_line('PGA before:  ' || pga_max_mb || ' MB');

  open c_orders;
  loop
    fetch c_orders bulk collect into l_orders limit 1000;
    exit when l_orders.count = 0;

    l_batches := l_batches + 1;
    l_rows    := l_rows + l_orders.count;
  end loop;
  close c_orders;

  dbms_output.put_line('Rows read:   ' || l_rows || ' in ' || l_batches || ' batches');
  dbms_output.put_line('Time:        ' || (dbms_utility.get_time - l_start) * 10 || ' ms');
  dbms_output.put_line('PGA peak:    ' || pga_max_mb || ' MB');
end;
/

Output:

PGA before:  6 MB
Rows read:   500000 in 500 batches
Time:        110 ms
PGA peak:    7 MB

PL/SQL procedure successfully completed.

The block read the same 500,000 rows in 500 batches, and the PGA peak stayed at 7 MB. It was also faster than loading everything at once, because the database did not have to allocate and fill a 250 MB collection. The memory now depends on the batch size, not on the number of rows.

Choose the LIMIT Value

This block reads the whole table six times with different batch sizes and prints the time for each:

Example:

declare
  cursor c_orders is select * from bulk_orders;
  type t_orders is table of c_orders%rowtype;
  l_orders t_orders;
  l_start  number;

  procedure read_all (p_limit pls_integer) is
    l_rows pls_integer := 0;
  begin
    l_start := dbms_utility.get_time;
    open c_orders;
    loop
      fetch c_orders bulk collect into l_orders limit p_limit;
      exit when l_orders.count = 0;
      l_rows := l_rows + l_orders.count;
    end loop;
    close c_orders;
    dbms_output.put_line('LIMIT ' || lpad(p_limit, 6) || ': '
      || lpad((dbms_utility.get_time - l_start) * 10, 5) || ' ms for '
      || l_rows || ' rows');
  end;
begin
  read_all(1);
  read_all(10);
  read_all(100);
  read_all(1000);
  read_all(10000);
  read_all(100000);
end;
/

Output:

LIMIT      1:   800 ms for 500000 rows
LIMIT     10:   160 ms for 500000 rows
LIMIT    100:   110 ms for 500000 rows
LIMIT   1000:   100 ms for 500000 rows
LIMIT  10000:    90 ms for 500000 rows
LIMIT 100000:   110 ms for 500000 rows

PL/SQL procedure successfully completed.
  • LIMIT 1 is the same as fetching one row at a time: 800 ms, about eight times slower than the rest.
  • From LIMIT 100 upward, the time hardly changes. Most of the gain comes from the first hundred rows per fetch.
  • Larger batches only cost memory. A value between 100 and 1,000 is a good default; use the lower end for wide rows (many columns or long VARCHAR2 and LOB columns), and measure before going above a few thousand.

The Exit Condition That Loses Rows

With single-row fetches, the usual loop exits on cursor%NOTFOUND right after the FETCH. With LIMIT, the same exit loses rows. This cursor returns 2,500 rows, and the loop exits on %NOTFOUND:

Example:

declare
  cursor c_orders is select * from bulk_orders where order_id <= 2500;
  type t_orders is table of c_orders%rowtype;
  l_orders t_orders;
  l_rows   pls_integer := 0;
begin
  open c_orders;
  loop
    fetch c_orders bulk collect into l_orders limit 1000;
    exit when c_orders%notfound;

    l_rows := l_rows + l_orders.count;
  end loop;
  close c_orders;

  dbms_output.put_line('Rows processed: ' || l_rows || ' of 2500');
end;
/

Output:

Rows processed: 2000 of 2500

PL/SQL procedure successfully completed.

500 rows were never processed. After a FETCH ... LIMIT, %NOTFOUND is TRUE when the fetch returned fewer rows than the limit, not only when it returned none.

The third fetch returned the last 500 rows and set %NOTFOUND, so the loop exited before processing them. The next block exits on an empty collection instead and prints %NOTFOUND after each batch:

Example:

declare
  cursor c_orders is select * from bulk_orders where order_id <= 2500;
  type t_orders is table of c_orders%rowtype;
  l_orders t_orders;
  l_rows   pls_integer := 0;
begin
  open c_orders;
  loop
    fetch c_orders bulk collect into l_orders limit 1000;
    exit when l_orders.count = 0;

    l_rows := l_rows + l_orders.count;
    dbms_output.put_line('Batch of ' || l_orders.count || ' rows, %NOTFOUND = '
      || case when c_orders%notfound then 'TRUE' else 'FALSE' end);
  end loop;
  close c_orders;

  dbms_output.put_line('Rows processed: ' || l_rows || ' of 2500');
end;
/

Output:

Batch of 1000 rows, %NOTFOUND = FALSE
Batch of 1000 rows, %NOTFOUND = FALSE
Batch of 500 rows, %NOTFOUND = TRUE
Rows processed: 2500 of 2500

PL/SQL procedure successfully completed.

Use EXIT WHEN collection.COUNT = 0 directly after the FETCH. If you prefer %NOTFOUND, put EXIT WHEN cursor%NOTFOUND at the end of the loop, after the batch has been processed.

When a Cursor FOR Loop Is Enough

A cursor FOR loop already fetches in batches. With the default PLSQL_OPTIMIZE_LEVEL of 2, the compiler turns it into array fetches of 100 rows. This block compares it with an explicit cursor that fetches one row at a time:

Example:

declare
  cursor c_orders is select * from bulk_orders;
  l_order bulk_orders%rowtype;
  l_rows  pls_integer := 0;
  l_start number := dbms_utility.get_time;
begin
  open c_orders;
  loop
    fetch c_orders into l_order;
    exit when c_orders%notfound;
    l_rows := l_rows + 1;
  end loop;
  close c_orders;
  dbms_output.put_line('Single-row FETCH:  '
    || lpad((dbms_utility.get_time - l_start) * 10, 5) || ' ms for ' || l_rows || ' rows');

  l_rows  := 0;
  l_start := dbms_utility.get_time;
  for r in (select * from bulk_orders) loop
    l_rows := l_rows + 1;
  end loop;
  dbms_output.put_line('Cursor FOR loop:   '
    || lpad((dbms_utility.get_time - l_start) * 10, 5) || ' ms for ' || l_rows || ' rows');
end;
/

Output:

Single-row FETCH:    620 ms for 500000 rows
Cursor FOR loop:     110 ms for 500000 rows

PL/SQL procedure successfully completed.

The cursor FOR loop read the 500,000 rows as fast as BULK COLLECT with LIMIT. So when a loop only reads rows and does work in PL/SQL, a cursor FOR loop is fine.

Use BULK COLLECT with LIMIT when the loop runs DML for each row: the collection can then feed a FORALL statement, which sends the whole batch to the SQL engine in one call. An explicit single-row FETCH loop is the slowest choice in every case.

Archive Rows in Batches with FORALL

A typical batch job: move all CLOSED orders to the archive table. The block fetches 1,000 orders at a time, inserts the batch into the archive with one FORALL statement, and deletes the same batch from the orders table with a second one:

Example:

declare
  cursor c_closed is
    select order_id, customer_id, order_date, amount, status, note
    from   bulk_orders
    where  status = 'CLOSED';

  type t_orders is table of c_closed%rowtype;
  l_orders  t_orders;
  c_batch   constant pls_integer := 1000;
  l_moved   pls_integer := 0;
  l_start   number := dbms_utility.get_time;
begin
  open c_closed;
  loop
    fetch c_closed bulk collect into l_orders limit c_batch;
    exit when l_orders.count = 0;

    forall i in 1 .. l_orders.count
      insert into bulk_orders_archive
        (order_id, customer_id, order_date, amount, status, note)
      values
        (l_orders(i).order_id, l_orders(i).customer_id, l_orders(i).order_date,
         l_orders(i).amount, l_orders(i).status, l_orders(i).note);

    forall i in 1 .. l_orders.count
      delete from bulk_orders where order_id = l_orders(i).order_id;

    l_moved := l_moved + l_orders.count;
  end loop;
  close c_closed;
  commit;

  dbms_output.put_line('Orders archived: ' || l_moved);
  dbms_output.put_line('Time:            '
    || (dbms_utility.get_time - l_start) * 10 || ' ms');
end;
/

select (select count(*) from bulk_orders)         as orders_left,
       (select count(*) from bulk_orders_archive) as archived
from   dual;

Output:

Orders archived: 125000
Time:            720 ms

PL/SQL procedure successfully completed.

   ORDERS_LEFT    ARCHIVED
______________ ___________
        375000      125000
  • The collection is declared on the cursor (c_closed%ROWTYPE), so it always matches the selected columns.
  • FORALL refers to the fields of each record, l_orders(i).order_id and so on. The archive table has an extra column, ARCHIVED_ON, which its default fills.
  • The 125,000 orders moved in 125 batches of 1,000 rows, with 250 FORALL calls in total instead of 250,000 single-row statements.
  • The block commits once, at the end. Committing inside the loop shortens the transaction, but a cursor that stays open across commits can then fail with ORA-01555 (snapshot too old), and a failure halfway leaves part of the orders moved. Commit inside the loop only when the job can restart from where it stopped.

Things to Know

  • LIMIT works only with FETCH. To cap a SELECT ... BULK COLLECT INTO, use FETCH FIRST n ROWS ONLY in the query; that returns only the first n rows, it does not read the rest in batches.
  • A FETCH with LIMIT replaces the collection's contents every time, so there is nothing to delete between batches. The collection is always densely filled from index 1, which is why FORALL i IN 1 .. collection.COUNT is safe.
  • BULK COLLECT never raises NO_DATA_FOUND. An empty fetch just returns an empty collection.
  • LIMIT also works with cursor variables (SYS_REFCURSOR), for example on a cursor returned by another procedure.
  • When a single row in a FORALL batch fails, the whole statement stops unless you add SAVE EXCEPTIONS and handle ORA-24381.

Related Guides

Conclusion

BULK COLLECT without LIMIT holds the whole result set in memory: 253 MB of PGA for 500,000 rows in this test. FETCH ... BULK COLLECT INTO ... LIMIT read the same rows with 7 MB, in the same time or less.

Pick a batch size between 100 and 1,000, and exit the loop when the collection is empty, never on %NOTFOUND right after the fetch.

For loops that only read, a cursor FOR loop already fetches 100 rows at a time. For loops that change data, BULK COLLECT with LIMIT and FORALL process any number of rows in batches with small, fixed memory.

Get more Vinish.dev tutorials on Google

See my articles more often when you search Google.

Tags
Vinish Kapoor
Vinish Kapoor

An Oracle ACE, author of four books on Oracle APEX, SQL and PL/SQL, and Oracle Forms, and a software developer building Oracle database applications since 2001.

guest

0 Comments
Oldest
Newest Most Voted