SQL> SQL> /* DOC>%NOTFOUND always evaluates to TRUE in a single batch BULK DOC>COLLECT or in the last batch of a multi-batch BULK COLLECT using the DOC>LIMIT clause - if it returns fewer rows than the limit size. DOC>So, you can not put %notfound immediately after FETCH..... LIMIT, or the last batch DOC>can not be processed after they have been fetched. DOC>*/ SQL> SQL> -- %rowcount and %notfound SQL> -- %NOTFOUND = true, even though 4 rows were retrieved in the last batch. SQL> SQL> declare 2 type nametab is table of emp.ename%type; 3 type saltab is table of emp.sal%type; 4 names nametab; 5 sals saltab; 6 cursor c1 is select ename, sal from emp; 7 begin 8 open c1; 9 loop 10 FETCH c1 BULK COLLECT INTO names, sals limit 5; 11 FOR i IN names.FIRST..names.LAST LOOP 12 DBMS_OUTPUT.PUT_LINE('row '||i||' '||names(i) || ' ' || sals(i)); 13 END LOOP; 14 if c1%found then 15 DBMS_OUTPUT.PUT_LINE('Found '||c1%rowcount||' rows'); 16 else 17 DBMS_OUTPUT.PUT_LINE('NOT FOUND'); 18 end if; 19 exit when c1%NOTFOUND; 20 end loop; 21 close c1; 22 end; 23 / row 1 ALLEN 1600 row 2 WARD 1250 row 3 MARTIN 1250 row 4 SCOTT 3000 row 5 KING 5000 Found 5 rows row 1 TURNER 1500 row 2 FORD 3000 row 3 DAVID 6000 row 4 jusung 9999.99 NOT FOUND PL/SQL procedure successfully completed. SQL> SQL> -- %bulk_rowcount(i) = # of rows collected when used with BULK COLLECT SQL> -- %Bulk_ROWCOUNT(null) changes during the FORALL loop SQL> SQL> declare 2 type nametab is table of emp.ename%type; 3 type saltab is table of emp.sal%type; 4 names nametab; 5 sals saltab; 6 cursor c1 is select ename, sal from emp; 7 begin 8 open c1; 9 loop 10 FETCH c1 BULK COLLECT INTO names, sals limit 4; 11 FOR i IN names.FIRST..names.LAST LOOP 12 DBMS_OUTPUT.PUT_LINE('row '||i||' '||names(i) || ' ' || sals(i)); 13 END LOOP; 14 if c1%found then 15 DBMS_OUTPUT.PUT_LINE('Found '||c1%rowcount||' Total so far:' 16 ||c1%Bulk_ROWCOUNT(null)||' rows'); 17 else 18 DBMS_OUTPUT.PUT_LINE('NOT FOUND'); 19 end if; 20 exit when c1%NOTFOUND; 21 end loop; 22 for i in 1..10 loop 23 DBMS_OUTPUT.PUT_LINE('Bulk_ROWCOUNT('||i||')= '||c1%Bulk_ROWCOUNT(i)); 24 end loop; 25 close c1; 26 end; 27 / row 1 ALLEN 1600 row 2 WARD 1250 row 3 MARTIN 1250 row 4 SCOTT 3000 Found 4 Total so far:4 rows row 1 KING 5000 row 2 TURNER 1500 row 3 FORD 3000 row 4 DAVID 6000 Found 8 Total so far:8 rows row 1 jusung 9999.99 NOT FOUND Bulk_ROWCOUNT(1)= 9 Bulk_ROWCOUNT(2)= 9 Bulk_ROWCOUNT(3)= 9 Bulk_ROWCOUNT(4)= 9 Bulk_ROWCOUNT(5)= 9 Bulk_ROWCOUNT(6)= 9 Bulk_ROWCOUNT(7)= 9 Bulk_ROWCOUNT(8)= 9 Bulk_ROWCOUNT(9)= 9 Bulk_ROWCOUNT(10)= 9 PL/SQL procedure successfully completed. SQL> SQL> -- Problem arises when the last batch is full SQL> declare 2 type nametab is table of emp.ename%type; 3 type saltab is table of emp.sal%type; 4 names nametab; 5 sals saltab; 6 cursor c1 is select ename, sal from emp; 7 begin 8 open c1; 9 loop 10 FETCH c1 BULK COLLECT INTO names, sals limit 3; 11 FOR i IN names.FIRST..names.LAST LOOP 12 DBMS_OUTPUT.PUT_LINE('row '||i||' '||names(i) || ' ' || sals(i)); 13 END LOOP; 14 if c1%found then 15 DBMS_OUTPUT.PUT_LINE('Found '||c1%rowcount||' Total so far:' 16 ||c1%Bulk_ROWCOUNT(null)||' rows'); 17 else 18 DBMS_OUTPUT.PUT_LINE('NOT FOUND'); 19 end if; 20 exit when c1%NOTFOUND; 21 end loop; 22 close c1; 23 end; 24 / row 1 ALLEN 1600 row 2 WARD 1250 row 3 MARTIN 1250 Found 3 Total so far:3 rows row 1 SCOTT 3000 row 2 KING 5000 row 3 TURNER 1500 Found 6 Total so far:6 rows row 1 FORD 3000 row 2 DAVID 6000 row 3 jusung 9999.99 Found 9 Total so far:9 rows declare * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 11 SQL> SQL> -- The fix - don't use %NOTFOUND, use array.count SQL> declare 2 type nametab is table of emp.ename%type; 3 type saltab is table of emp.sal%type; 4 names nametab; 5 sals saltab; 6 cursor c1 is select ename, sal from emp; 7 begin 8 open c1; 9 loop 10 FETCH c1 BULK COLLECT INTO names, sals limit 3; 11 exit when names.count=0; 12 FOR i IN names.FIRST..names.LAST LOOP 13 DBMS_OUTPUT.PUT_LINE('row '||i||' '||names(i) || ' ' || sals(i)); 14 END LOOP; 15 if c1%found then 16 DBMS_OUTPUT.PUT_LINE('Found '||c1%rowcount||' Total so far:' 17 ||c1%Bulk_ROWCOUNT(null)||' rows'); 18 else 19 DBMS_OUTPUT.PUT_LINE('NOT FOUND'); 20 end if; 21 end loop; 22 close c1; 23 end; 24 / row 1 ALLEN 1600 row 2 WARD 1250 row 3 MARTIN 1250 Found 3 Total so far:3 rows row 1 SCOTT 3000 row 2 KING 5000 row 3 TURNER 1500 Found 6 Total so far:6 rows row 1 FORD 3000 row 2 DAVID 6000 row 3 jusung 9999.99 Found 9 Total so far:9 rows PL/SQL procedure successfully completed. SQL> SQL> -- %bulk_rowcount(i) should be 1 with insert, if insert succeeds SQL> SQL> declare 2 type tab_rec is table of test1%RowType; 3 x tab_rec:=tab_rec(); 4 begin 5 x.extend; 6 x(1).c1:='F'; 7 x(1).c2:=0; 8 x(1).sumc2:=222; 9 x.extend; 10 x(2).c1:='L'; 11 x(2).c2:=3; 12 x(2).sumc2:=123; 13 14 FORALL i in 1..x.count 15 insert into test1 values x(i); 16 17 for i in 1..x.count loop 18 dbms_output.put_line(sql%bulk_rowcount(i)); 19 end loop; 20 21 end; 22 / 1 1 PL/SQL procedure successfully completed. SQL> SQL> rollback; Rollback complete. SQL> SQL> spool off