oracle bulk collect limit clause execution details using the sys refcursor

Question

I am creating a stored procedure to bulk collect the data from one table (table 1) to another table (table 2) using limit clause .

For example if I am loading 80000 records with the limit clause of 1000 how many times the select statement in the sys refcursor gets executed? Is it going to execute once or for each limit's iteration (80000/1000) = 80 times?

Please could someone provide more details on the processing .

code skeleton snippet

create or replace procedure <procedure_name> as
<curosor name> SYS_REFCURSOR;
< collection_name > ;
begin
  open <cursor_name> for <select statment>;
  loop 
    fetch <cursor_name> into < collection_name >  limit 1000;
    exit when <cursor_name>%not_found;
    forall i in 1..<collection_name>.count
        insert statement into to table 2 values <i>
  end loop;

It has been awhile since I used bulk collect/forall in this way, but I'd put it together directly as your pseudo code has it. One thing to check. I seem to remember with the limit clause Not_Found is true when the limit is not reached, so if only 900 rows exist in the last iteration they would not be processed. Of course with a mere 80,000 rows I would not bother with a loop and just do *insert into table(...) select ,,,*. — Belayer, Mar 17 '20 at 16:32

score 1 · Answer 1 · answered Mar 17 '20 at 17:50

The database will execute the cursor once. But it will fetch from it ( # rows / limit ) + 1 times.

You can verify this by tracing the session and formatting the trace file:

create table t (
  c1 int
);

exec dbms_monitor.session_trace_enable ( waits => true, binds => true );
declare
  cur sys_refcursor; 

  arr dbms_sql.number_table;
begin
  open cur for
    select level c1 from dual
    connect by level <= 10000;
  loop
    fetch cur 
    bulk collect into arr
    limit 100;
    exit when arr.count = 0;

    forall rw in 1 .. arr.count 
      insert into t values ( arr ( rw ) );
  end loop;
  close cur;
end;
/
exec dbms_monitor.session_trace_disable ();

This will generate a trace file on the database server. You can find the location with this query:

select value
from   v$diag_info
where  name = 'Default Trace File';

Use TKPROF to format the file and you'll see something like this:

SELECT LEVEL C1 
FROM
 DUAL CONNECT BY LEVEL <= 10000


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      101      0.00       0.01          0          0          0       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      103      0.01       0.01          0          0          0       10000

Execute = 1, Fetch = 101

oracle bulk collect limit clause execution details using the sys refcursor

1 Answers1