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.

Solution

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;

Comments

[Martin]

The problem is that I can't use %ROWTYPE because my data comes from many tables in my real proc. Edited code sample to reflect that

[Tony Andrews]

You can't do it generically I agree - in fact you probably can't do what you want. The "do something" code must know the structure of the data it is going to use, and so it will have to take care of the situation where the cursor returns no rows.

[Alex Poole]

You can declare a record type to fetch into, matching the expected output field types from the underlying query. (Hopefully you've simplified but using * isn't ideal; among other things you've got two columns called ID).

[Martin]

The do something part is just used for reporting some information in a special case. I find it a bit weird that I would have to duplicate record structure. It means if the query changes, I will have to update the record structure when I just want to check if it returns data. Maybe it is me who stands too much against repetition, but to me, it seems wrong that I have to repeat the structure.

[Bob Jarvis - Слава Україні]

@Martin: OK, you can't use %ROWTYPE - then you'll have to use "INTO var_1, var_2, ... var_etc". And the cursor returned by Foo will therefore need to have a "real" field list, not just "*".

[Alex Poole]

@Martin: if you only ever want to know whether there is any data returned or not, and the actual data isn't used; then why are you using a cursor at all? Why not just have a function that returns the count() of rows as number, or have a function than returns boolean - which could potentially use a rownum restriction to reduce the load?

[Alex Poole]

@Myself: ah, because you do need the actual data on the asp.net side, so you'd have to maintain two almost identical queries.

[Martin]

I ended up declaring the record type in the package and fetching into a variable of that record type.