Fetching a dynamic query one row at a time with DBMS_SQL is slow for large results. DEFINE_ARRAY makes each FETCH_ROWS return a batch of rows into a collection, the DBMS_SQL counterpart of BULK COLLECT ... LIMIT.
Code for This Guide
The main examples are in the examples/pkg-dbms-sql folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_sql.define_array(c, position, collection, batch_size, lower_index); rows := dbms_sql.execute(c); loop rows := dbms_sql.fetch_rows(c); -- up to batch_size rows dbms_sql.column_value(c, position, collection); exit when rows < batch_size; end loop;
LOWER_INDEX is the index where the first fetched value is stored.
Fetch in Batches of Ten
Example:
declare
c integer := dbms_sql.open_cursor;
v_codes dbms_sql.varchar2_table;
v_n integer;
begin
dbms_sql.parse(c, 'select airport_code from airports order by 1', dbms_sql.native);
dbms_sql.define_array(c, 1, v_codes, 10, 1); -- fetch 10 rows at a time
v_n := dbms_sql.execute(c);
loop
v_n := dbms_sql.fetch_rows(c);
dbms_sql.column_value(c, 1, v_codes);
exit when v_n < 10;
end loop;
dbms_output.put_line(v_codes.count || ' codes, from ' || v_codes(v_codes.first)
|| ' to ' || v_codes(v_codes.last));
dbms_sql.close_cursor(c);
end;
/Output:
22 codes, from AKL to YYZ PL/SQL procedure successfully completed.
The 22 airport codes arrive in three fetches. COLUMN_VALUE adds each batch to the collection, so at the end it holds all 22, from AKL to YYZ. The loop ends after a batch smaller than ten.
One Batch at a Time
To process each batch and keep memory small, empty the collection before each COLUMN_VALUE.
Example:
declare
c integer := dbms_sql.open_cursor;
v_codes dbms_sql.varchar2_table;
v_n integer;
begin
dbms_sql.parse(c, 'select airport_code from airports order by 1', dbms_sql.native);
dbms_sql.define_array(c, 1, v_codes, 10, 1);
v_n := dbms_sql.execute(c);
loop
v_n := dbms_sql.fetch_rows(c);
v_codes.delete; -- start each batch empty
dbms_sql.column_value(c, 1, v_codes);
dbms_output.put_line('batch of ' || v_n || ': indexes ' || v_codes.first
|| ' to ' || v_codes.last);
exit when v_n < 10; -- a short batch is the last one
end loop;
dbms_sql.close_cursor(c);
end;
/Output:
batch of 10: indexes 1 to 10 batch of 10: indexes 11 to 20 batch of 2: indexes 21 to 22 PL/SQL procedure successfully completed.
Each batch has its 10, 10, and 2 rows, but the indexes keep counting from where the previous batch ended, even after DELETE. Loop from FIRST to LAST rather than from 1.
Things to Know
- Exit after a short batch: fetching again after the last rows raises ORA-01002, fetch out of sequence.
- Define every column of the query with its own collection, all with the same batch size.
- For queries known at compile time, BULK COLLECT ... LIMIT is simpler.
Related Guides
Conclusion
DBMS_SQL.DEFINE_ARRAY fetches dynamic query results in batches into collections. Choose a batch size, stop after a short batch, and loop over FIRST to LAST, since each batch continues the indexes of the one before.
