%found after a bulk collect

Today I was reminded of a mistake I so often make:

OPEN  c_somecursor;
FETCH c_somecursor BULK COLLECT INTO t_somecursor;
l_found := c_somecursor%FOUND;
CLOSE c_somecursor;

IF l_found
THEN
 dbms_output.put_line('Jup, found something');

The title of this post should give anough hints where this goes wrong. Even if there are records fetched %FOUND does not give back TRUE.
This does work:

OPEN  c_somecursor;
FETCH c_somecursor BULK COLLECT INTO t_somecursor;
CLOSE c_somecursor;

IF t_somecursor.count > 0
THEN
 dbms_output.put_line('Jup, found something');

Thanks to a colleague of mine, Erik:
%FOUND only works with a BULK COLLECT if this BULK COLLECT is limited:

OPEN  c_somecursor;
FETCH c_somecursor BULK COLLECT INTO t_somecursor LIMIT 2;
l_found := c_somecursor%FOUND;
CLOSE c_somecursor;

IF l_found
THEN
 dbms_output.put_line('Jup, found something');

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>