how to check if a ref cursor returns data from a pl/sql procedure

Question

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.

Answer 1

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;