I would like to know how to check if a ref cursor returns data.
Let's say I have the following code in a PL/SQL package:
type refcursor is ref cursor;
procedure Foo(cursorresult out refcursor) is
begin
open cursorresult for
select *
from table t
inner join t2 on t.id = t2.id
where t.column1 is null;
end;
procedure DoSomeghingIfFooHasResults is
curFoo refcursor;
begin
Foo(curSansOwner);
if curFoo%found then
-- Do something
end if;
end function;
This code is used in a more involved process and the query in Foo is using multiple tables.
I need the data returned from Foo in an asp.net application, but I also need to do something when Foo finds some data.
I want to reuse the query at a few places, but I don't think this would be a good candidate for a view.
What would be the best way to know if Foo finds something ?
Thanks.
The only way to know if it has found something is to FETCH from it:
procedure DoSomeghingIfFooHasResults is
curFoo refcursor;
recFoo mytable%ROWTYPE;
begin
Foo(curFoo);
fetch curFoo into recFoo;
if curFoo%found then
-- Do something
end if;
end function;