bulk insert in a loop failed

[denis]

I recieved ORA-06502 when I put the bulk insert in a loop. what could
be the reason? Any help to fix it is greatly appreciated.


SQL>desc driver;
Name
Null? Type
-----------------------------------------------------------------
-------- --------------

ID
NUMBER

SQL>desc temp_1;
Name
Null? Type
-----------------------------------------------------------------
-------- --------------

ID
NUMBER

===> test 1 without loop is ok
SQL>@test1
SQL>declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;
5 cursor c is select id from driver;
6 begin
7 open c;
8 -- loop
9 fetch c bulk collect into ids limit 10;
10 forall i in ids.first..ids.last
11 insert into temp_1 values ids(i);
12 -- exit when c%notfound;
13 -- end loop;
14 close c;
15 end;
16 /

PL/SQL procedure successfully completed.

===> test 2 with loop failed

SQL>@test2
SQL>declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;
5 cursor c is select id from driver;
6 begin
7 open c;
8 loop
9 fetch c bulk collect into ids limit 10;
10 forall i in ids.first..ids.last
11 insert into temp_1 values ids(i);
12 exit when c%notfound;
13 end loop;
14 close c;
15 end;
16 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 10

[Trail]

Hi Denis,

Just thought I'd throw in my $.02. The thing that stands out at me
would be the use of "first" and "last" on line 10. I would simply
use:

forall i in 1 .. ids.count

It's worth a shot. I'm not familiar with the other syntax (though it
did work the first time).

Good luck

-T.

[denis]

Hi, T

Thanks. 1 .. ids.count seems working as I wanted.

> > ORA-06512: at line 10- Hide quoted text -
>
> - Show quoted text -

[denis]

In the following test case, why we can not get all rows inserted into
temp_1 (98 row instead of 100 rows)

====> this test shows not all rows are inserted

SQL>@test2a
SQL>
SQL>select count(*) from temp_1;

COUNT(*)
----------
0

SQL>select count(*) from driver;

COUNT(*)
----------
100

SQL>

SQL>declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;
5 cursor c is select id from driver;

6 cnt number;
7 begin
8 open c;
9 loop
10 fetch c bulk collect into ids limit 14;
11 exit when c%notfound;
12 forall i in ids.first..ids.last
13 insert into temp_1 values ids(i);
14 select count(*) into cnt from temp_1;
15 dbms_output.put_line('temp_1 # of rows: ' || cnt);
16 end loop;
17 close c;
18 end;
19 /
temp_1 # of rows: 14
temp_1 # of rows: 28
temp_1 # of rows: 42
temp_1 # of rows: 56
temp_1 # of rows: 70
temp_1 # of rows: 84
temp_1 # of rows: 98

PL/SQL procedure successfully completed.

SQL>select count(*) from temp_1;

COUNT(*)
----------
98

SQL>select count(*) from driver;

COUNT(*)
----------
100

> > - Show quoted text -- Hide quoted text -

[ddf]

It's because you're using %NOTFOUND in a bulk collect operation:

SQL> --
SQL> -- Create driver table
SQL> --
SQL> create table driver(
2 id number,
3 val varchar2(40)
4 );

Table created.

SQL>
SQL> --
SQL> -- Create destination table
SQL> --
SQL> create table temp_1
2 as select *
3 from driver;

Table created.

SQL>
SQL> --
SQL> -- Load driver table
SQL> --
SQL> begin
2 for i in 1..100 loop
3 insert into driver
4 values(i, 'Record '||i);
5 end loop;
6
7 commit;
8
9 end;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Check counts
SQL> --

SQL> select count(*) from temp_1;

COUNT(*)
----------
0

SQL>
SQL> select count(*) from driver;

COUNT(*)
----------
100

SQL>

SQL> --
SQL> -- Non-working example
SQL> --

SQL> declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;

5 cursor c is select id, val from driver;

6 cnt number;
7 begin
8 open c;
9 loop
10 fetch c bulk collect into ids limit 14;

11 exit when c%notfound; <---- This is causing you to not
insert all of your records

12 forall i in ids.first..ids.last
13 insert into temp_1 values ids(i);
14 select count(*) into cnt from temp_1;
15 dbms_output.put_line('temp_1 # of rows: ' || cnt);
16 end loop;
17 close c;
18 end;
19 /
temp_1 # of rows: 14
temp_1 # of rows: 28
temp_1 # of rows: 42
temp_1 # of rows: 56
temp_1 # of rows: 70
temp_1 # of rows: 84
temp_1 # of rows: 98

PL/SQL procedure successfully completed.

SQL>

SQL> --
SQL> -- Truncate temp_1
SQL> --
SQL>
SQL> truncate table temp_1;

Table truncated.

SQL>
SQL> --
SQL> -- Working example
SQL> --

SQL> declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;

5 cursor c is select id, val from driver;

6 cnt number;
7 begin
8 open c;
9 loop
10 fetch c bulk collect into ids limit 14;

11 exit when ids.count = 0; <---- This fixes your coding error

12 forall i in ids.first..ids.last
13 insert into temp_1 values ids(i);
14 select count(*) into cnt from temp_1;
15 dbms_output.put_line('temp_1 # of rows: ' || cnt);
16 end loop;
17 close c;
18 end;
19 /
temp_1 # of rows: 14
temp_1 # of rows: 28
temp_1 # of rows: 42
temp_1 # of rows: 56
temp_1 # of rows: 70
temp_1 # of rows: 84
temp_1 # of rows: 98

temp_1 # of rows: 100

PL/SQL procedure successfully completed.

SQL>

David Fitzjarrell

[ddf]

> David Fitzjarrell- Hide quoted text -

>
> - Show quoted text -

To better explain why %NOTFOUND doesn't work with the LIMIT clause,
let's look at some examples where the LIMIT is an even divisor of the
source row count and %NOTFOUND appears to work:

SQL>
SQL> --
SQL> -- Yet another working example

SQL> --
SQL> declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;
5 cursor c is select id, val from driver;
6 cnt number;
7 begin
8 open c;
9 loop

10 fetch c bulk collect into ids limit 2;

11 exit when c%notfound;
12 forall i in ids.first..ids.last
13 insert into temp_1 values ids(i);
14 select count(*) into cnt from temp_1;
15 dbms_output.put_line('temp_1 # of rows: ' || cnt);
16 end loop;
17 close c;
18 end;
19 /

temp_1 # of rows: 2
temp_1 # of rows: 4
temp_1 # of rows: 6
temp_1 # of rows: 8
temp_1 # of rows: 10
temp_1 # of rows: 12

temp_1 # of rows: 14

temp_1 # of rows: 16
temp_1 # of rows: 18
temp_1 # of rows: 20
temp_1 # of rows: 22
temp_1 # of rows: 24
temp_1 # of rows: 26

temp_1 # of rows: 28

temp_1 # of rows: 30
temp_1 # of rows: 32
temp_1 # of rows: 34
temp_1 # of rows: 36
temp_1 # of rows: 38
temp_1 # of rows: 40

temp_1 # of rows: 42

temp_1 # of rows: 44
temp_1 # of rows: 46
temp_1 # of rows: 48
temp_1 # of rows: 50
temp_1 # of rows: 52
temp_1 # of rows: 54

temp_1 # of rows: 56

temp_1 # of rows: 58
temp_1 # of rows: 60
temp_1 # of rows: 62
temp_1 # of rows: 64
temp_1 # of rows: 66
temp_1 # of rows: 68

temp_1 # of rows: 70

temp_1 # of rows: 72
temp_1 # of rows: 74
temp_1 # of rows: 76
temp_1 # of rows: 78
temp_1 # of rows: 80
temp_1 # of rows: 82

temp_1 # of rows: 84

temp_1 # of rows: 86
temp_1 # of rows: 88
temp_1 # of rows: 90
temp_1 # of rows: 92
temp_1 # of rows: 94
temp_1 # of rows: 96

temp_1 # of rows: 98
temp_1 # of rows: 100

PL/SQL procedure successfully completed.

SQL>

SQL> --
SQL> -- Truncate temp_1
SQL> --
SQL>
SQL> truncate table temp_1;

Table truncated.

SQL>
SQL> --

SQL> -- Yet another working example

SQL> --
SQL> declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;
5 cursor c is select id, val from driver;
6 cnt number;
7 begin
8 open c;
9 loop

10 fetch c bulk collect into ids limit 5;

11 exit when c%notfound;
12 forall i in ids.first..ids.last
13 insert into temp_1 values ids(i);
14 select count(*) into cnt from temp_1;
15 dbms_output.put_line('temp_1 # of rows: ' || cnt);
16 end loop;
17 close c;
18 end;
19 /

temp_1 # of rows: 5
temp_1 # of rows: 10
temp_1 # of rows: 15
temp_1 # of rows: 20
temp_1 # of rows: 25
temp_1 # of rows: 30
temp_1 # of rows: 35
temp_1 # of rows: 40
temp_1 # of rows: 45
temp_1 # of rows: 50
temp_1 # of rows: 55
temp_1 # of rows: 60
temp_1 # of rows: 65

temp_1 # of rows: 70

temp_1 # of rows: 75
temp_1 # of rows: 80
temp_1 # of rows: 85
temp_1 # of rows: 90
temp_1 # of rows: 95

temp_1 # of rows: 100

PL/SQL procedure successfully completed.

SQL>

SQL> --
SQL> -- Truncate temp_1
SQL> --
SQL>
SQL> truncate table temp_1;

Table truncated.

SQL>
SQL> --

SQL> -- Yet another working example

SQL> --
SQL> declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;
5 cursor c is select id, val from driver;
6 cnt number;
7 begin
8 open c;
9 loop

10 fetch c bulk collect into ids limit 10;

11 exit when c%notfound;
12 forall i in ids.first..ids.last
13 insert into temp_1 values ids(i);
14 select count(*) into cnt from temp_1;
15 dbms_output.put_line('temp_1 # of rows: ' || cnt);
16 end loop;
17 close c;
18 end;
19 /

temp_1 # of rows: 10
temp_1 # of rows: 20
temp_1 # of rows: 30
temp_1 # of rows: 40
temp_1 # of rows: 50
temp_1 # of rows: 60

temp_1 # of rows: 70

temp_1 # of rows: 80
temp_1 # of rows: 90

temp_1 # of rows: 100

PL/SQL procedure successfully completed.

SQL>

SQL> --
SQL> -- Truncate temp_1
SQL> --
SQL>
SQL> truncate table temp_1;

Table truncated.

SQL>
SQL> --

SQL> -- Yet another working example

SQL> --
SQL> declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;
5 cursor c is select id, val from driver;
6 cnt number;
7 begin
8 open c;
9 loop

10 fetch c bulk collect into ids limit 20;

11 exit when c%notfound;
12 forall i in ids.first..ids.last
13 insert into temp_1 values ids(i);
14 select count(*) into cnt from temp_1;
15 dbms_output.put_line('temp_1 # of rows: ' || cnt);
16 end loop;
17 close c;
18 end;
19 /

temp_1 # of rows: 20
temp_1 # of rows: 40
temp_1 # of rows: 60
temp_1 # of rows: 80

temp_1 # of rows: 100

PL/SQL procedure successfully completed.

SQL>

SQL> --
SQL> -- Truncate temp_1
SQL> --
SQL>
SQL> truncate table temp_1;

Table truncated.

SQL>
SQL> --

SQL> -- Yet another working example

SQL> --
SQL> declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;
5 cursor c is select id, val from driver;
6 cnt number;
7 begin
8 open c;
9 loop

10 fetch c bulk collect into ids limit 50;

11 exit when c%notfound;
12 forall i in ids.first..ids.last
13 insert into temp_1 values ids(i);
14 select count(*) into cnt from temp_1;
15 dbms_output.put_line('temp_1 # of rows: ' || cnt);
16 end loop;
17 close c;
18 end;
19 /

temp_1 # of rows: 50

temp_1 # of rows: 100

PL/SQL procedure successfully completed.

SQL>

SQL> --
SQL> -- Truncate temp_1
SQL> --
SQL>
SQL> truncate table temp_1;

Table truncated.

In these cases %NOTFOUND is not set until after all of the data has
been collected, thus all of the rows are inserted into the destination
table. In cases like yours, and in the following examples, the LIMIT
is *not* an even divisor of the source row count so the last bulk
fetch is incomplete, %NOTFOUND is set and the loop exits, leaving the
destination table with fewer than the total number of source records
inserted. The number of records the destination is short is
determined by the LIMIT set in the bulk collect operation:

SQL>
SQL> --
SQL> -- Another non-working example

SQL> --
SQL> declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;
5 cursor c is select id, val from driver;
6 cnt number;
7 begin
8 open c;
9 loop

10 fetch c bulk collect into ids limit 38;

11 exit when c%notfound;
12 forall i in ids.first..ids.last
13 insert into temp_1 values ids(i);
14 select count(*) into cnt from temp_1;
15 dbms_output.put_line('temp_1 # of rows: ' || cnt);
16 end loop;
17 close c;
18 end;
19 /

temp_1 # of rows: 38
temp_1 # of rows: 76

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Truncate temp_1
SQL> --
SQL>
SQL> truncate table temp_1;

Table truncated.

SQL>
SQL> --

SQL> -- Another non-working example

SQL> --
SQL> declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;
5 cursor c is select id, val from driver;
6 cnt number;
7 begin
8 open c;
9 loop

10 fetch c bulk collect into ids limit 27;

11 exit when c%notfound;
12 forall i in ids.first..ids.last
13 insert into temp_1 values ids(i);
14 select count(*) into cnt from temp_1;
15 dbms_output.put_line('temp_1 # of rows: ' || cnt);
16 end loop;
17 close c;
18 end;
19 /

temp_1 # of rows: 27
temp_1 # of rows: 54
temp_1 # of rows: 81

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Truncate temp_1
SQL> --
SQL>
SQL> truncate table temp_1;

Table truncated.

SQL>
SQL> --

SQL> -- Another non-working example

SQL> --
SQL> declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;
5 cursor c is select id, val from driver;
6 cnt number;
7 begin
8 open c;
9 loop

10 fetch c bulk collect into ids limit 17;

11 exit when c%notfound;
12 forall i in ids.first..ids.last
13 insert into temp_1 values ids(i);
14 select count(*) into cnt from temp_1;
15 dbms_output.put_line('temp_1 # of rows: ' || cnt);
16 end loop;
17 close c;
18 end;
19 /

temp_1 # of rows: 17
temp_1 # of rows: 34
temp_1 # of rows: 51
temp_1 # of rows: 68
temp_1 # of rows: 85

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Truncate temp_1
SQL> --
SQL>
SQL> truncate table temp_1;

Table truncated.

SQL>
SQL> --

SQL> -- Another non-working example

SQL> --
SQL> declare
2 type idTyp is table of driver%rowtype
3 index by binary_integer;
4 ids idTyp;
5 cursor c is select id, val from driver;
6 cnt number;
7 begin
8 open c;
9 loop

10 fetch c bulk collect into ids limit 11;

11 exit when c%notfound;
12 forall i in ids.first..ids.last
13 insert into temp_1 values ids(i);
14 select count(*) into cnt from temp_1;
15 dbms_output.put_line('temp_1 # of rows: ' || cnt);
16 end loop;
17 close c;
18 end;
19 /

temp_1 # of rows: 11
temp_1 # of rows: 22
temp_1 # of rows: 33
temp_1 # of rows: 44
temp_1 # of rows: 55
temp_1 # of rows: 66
temp_1 # of rows: 77
temp_1 # of rows: 88
temp_1 # of rows: 99

PL/SQL procedure successfully completed.

SQL>

For a bulk collect to function properly using LIMIT you need to not
exit the loop until the collection count is 0. The above non-working
examples will work when 'c%notfound' is replaced with 'ids.count = 0'.



David Fitzjarrell

[denis]

@ddf

Thanks. However, It seems I asked "why c%notfound dose not work" and
you answered "because you use c%notfound". No new info I get. Is c
%notfound not working in bulk collect well-known or documented
somewhere in Oracle manual?

Denis

> read more »- Hide quoted text -
>
> - Show quoted text -...

[ddf]

On May 9, 7:18 pm, denis <denis....@yahoo.com> wrote:
> @ddf
>
> Thanks. However, It seems I asked "why c%notfound dose not work" and
> you answered "because you use c%notfound". No new info I get. Is c
> %notfound not working in bulk collect well-known or documented
> somewhere in Oracle manual?
>
> Denis
>

You did get more info in a follow-up post I made to my original
response. You should read that follow-up befoe you needlessly
complain, as the reason was clearly given.


David Fitzjarrell

[denis]

Recently came across Steven Feuerstein's article:
http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html .
After reading it, I realized that the simple fact is when using limit
clause in the bulk collect, if one sets limint to be n, and if Oralce
is unable to fetch n records from the cursor (even being able to fetch
n-1 records), then Oracle will evaluate c%notfound to be TRUE. If I
knew this fact, I could explain what I observed in my test case
easily.

Thanks David for taking time to look at my question. You are
absolutely correct to indicate the reason is my using c%notfound in
the bulk collect loop (Steven's article also mentioned that it should
not be used in such case at all), I am just not smart enough to get
it .


Denis

[ddf]

As I mentioned in a prior post I also mentioned this in my follow-up
to my original response to your question, and I proved it with several
examples, both 'working' and not.

I won't repost the information as it's already part of this thread.


David Fitzjarrell

[denis]

This one works:


drop table driver;
drop table temp_1;

create table driver
as
select level id
from dual
connect by level <=100;

create table temp_1 as select * from driver where 1=0;

declare

type idTyp is table of driver%rowtype

index by binary_integer;
ids idTyp;

cursor c is select id from driver;

cnt number;
begin
open c;
loop

fetch c bulk collect into ids limit 14;

forall i in 1..ids.count

insert into temp_1 values ids(i);

select count(*) into cnt from temp_1;

dbms_output.put_line('temp_1 count: ' || cnt);
exit when c%notfound;
end loop;
close c;
end;
/