Re: Problem using c_cursor%FOUND

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 25 Sep 2002 12:04:59 -0700
Message-ID: <130ba93a.0209251104.642eb2f5@posting.google.com>

Your observations are correct. This is not a bug either - according to ORACLE. %NOTFOUND always evaluates to TRUE in a single batch BULK COLLECT or in the last batch of a multi-batch BULK COLLECT using the LIMIT clause - if it returns fewer rows than the limit size. It is best to use %ROWCOUNT as you are already using.

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 SMITH 800
row 2 ALLEN 1600
row 3 WARD 1250
row 4 JONES 2975
row 5 MARTIN 1250
Found 5 rows
row 1 BLAKE 2850
row 2 CLARK 2450
row 3 SCOTT 3000
row 4 KING 5000
row 5 TURNER 1500
Found 10 rows
row 1 ADAMS 1100
row 2 JAMES 950
row 3 FORD 3000
row 4 MILLER 1300
row 5 JOHN 10000
Found 15 rows
row 1 DAVID 6000
NOT FOUND PL/SQL procedure successfully completed.

SQL>
C Chang <cschang_at_maxinter.net> wrote in message news:<3D913223.6812_at_maxinter.net>...
> Why I can not use the c_cursor%FOUND when c_cursor is open and BULK
> COLLECT into a table type variable? I have to use the c_cursor%ROWCOUNT
> to check the c_cursor conditon. ex
>
> OPEN c_cursor;
> FETCH c_cursor BULK COLLECT INTO t_variable;
>
> IF c_cursor%ROWCOUNT > 0 THEN
> ...do something..
> END IF;
>
> CLOSE c_cursor;
>
> When I replaced the c_cursor%ROWCOUNT > 0 with c_cursor%FOUND, I never
> get into do something part. I have searched the documentation and found
> nothing about this restriction. Am I right?
>
> C Chang
Received on Wed Sep 25 2002 - 14:04:59 CDT