Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 11, 2019 - 9:34 pm UTC

Last updated: January 13, 2019 - 6:39 am UTC

Version: 12.0

Viewed 1000+ times

You Asked

Hi,

I want insert 500002 rows to my table by applying LIMIT with 10k , now how many times my LIMIT is iterate. Is it 5 times or 6 times.

Could plz give a sample example ?

and Connor said...

It will be:

ceil( 500002 / 10k ))

which is neither 5 or 6 :-)

SQL> select ceil( 500002  / 10000 ) from dual;

CEIL(500002/10000)
------------------
                51


But assuming you meant 50002, here's a demo

SQL> create table t_source ( x int);

Table created.

SQL> create table t_target ( x int );

Table created.

SQL>
SQL> insert into t_source
  2  select rownum from dual
  3  connect by level <= 50002;

50002 rows created.

SQL>
SQL> set serverout on
SQL> declare
  2    type array is table of int index by pls_integer;
  3    a array;
  4    cursor c is select * from t_source;
  5    iteration_count int := 0;
  6  begin
  7    open c;
  8    loop
  9      fetch c bulk collect into a limit 10000;
 10      exit when a.count = 0;
 11      iteration_count := iteration_count + 1;
 12      dbms_output.put_line('This is iteration '||iteration_count);
 13
 14      forall i in 1 .. a.count
 15         insert into t_target values (a(i));
 16
 17    end loop;
 18    close c;
 19  end;
 20  /
This is iteration 1
This is iteration 2
This is iteration 3
This is iteration 4
This is iteration 5
This is iteration 6

PL/SQL procedure successfully completed.



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library