On May 8, 7:29 am, ddf <orat...@msn.com> wrote:
> On May 7, 8:35 pm, denis <denis....@yahoo.com> wrote:
>
>
>
>
>
> > 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
>
> 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- 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
