Home » SQL & PL/SQL » SQL & PL/SQL » using cursor returned from another stored proc (10g)
using cursor returned from another stored proc [message #411042] Wed, 01 July 2009 12:21 Go to next message
Supertd
Messages: 15
Registered: December 2008
Location: Africa
Junior Member
Hi,
I am trying to use a cursor returned from another stored proc in my stored proc but cant seem to be able to open it.
Example:
function abc() return boolean is
  myCur p_ref_cursor;
  myInt number;
begin
 myInt:=4;
 --call another proc that takes an int ant returns a cursor
 procX(myInt, myCur);

 --now I want to check if myCur contains any rows
 open myCur;   --this is the line that the error occurs on
 if myCur%NOTFOUND --ie, has no rows
   return true;
 else return false;
 end if;
end;

The error I get is :

Error: PLS-00382: expression is of wrong type
Line: xx
Text: open myCur;

Can anyone please assist.
Thanks in advance.
Re: using cursor returned from another stored proc [message #411045 is a reply to message #411042] Wed, 01 July 2009 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is "p_ref_cursor"?

In addition, procX should open the cursor otherwise it is not defined.

In the end, %NOTFOUND is set only after you FETCH not after OPEN.

Regards
Michel
Re: using cursor returned from another stored proc [message #411103 is a reply to message #411042] Wed, 01 July 2009 23:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Typicslly, the OPEN would be done in procX.
Can you show us the relevant part of procX where you give the out-parameter a value?
Re: using cursor returned from another stored proc [message #411151 is a reply to message #411042] Thu, 02 July 2009 03:05 Go to previous messageGo to next message
Supertd
Messages: 15
Registered: December 2008
Location: Africa
Junior Member
Hi,
procX does open the cursor before returning it. p_ref_cursor is of type REF CURSOR. Is it then ok for me to just say
if myCur%not found
without using an open statement again? Is there any other way that I can check if any rows are returned in the cursor without having to loop through it?
Thanks.
Re: using cursor returned from another stored proc [message #411157 is a reply to message #411151] Thu, 02 July 2009 03:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You are correct in that you don't have to open it again. However, before you can tell whether or not it has any rows (i.e. checking the %NOT_FOUND), you need to FETCH first.
Re: using cursor returned from another stored proc [message #411167 is a reply to message #411151] Thu, 02 July 2009 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare
  2    c sys_refcursor;
  3    v varchar2(10);
  4    function status return varchar2 is
  5    begin
  6      if c%notfound then return 'not found';
  7      elsif not c%notfound then return 'found';
  8      else return 'unknown';
  9      end if;
 10    end;
 11  begin
 12    open c for select * from dual;
 13    dbms_output.put_line('before fetch: '||status);
 14    fetch c into v;
 15    dbms_output.put_line('after fetch: '||status);
 16  end;
 17  /
before fetch: unknown
after fetch: found

PL/SQL procedure successfully completed.

Regards
Michel
icon5.gif  Re: using cursor returned from another stored proc [message #411173 is a reply to message #411042] Thu, 02 July 2009 04:17 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
why can't you return the rowcount instead of the whole cursor from your procedure?
icon7.gif  Re: using cursor returned from another stored proc [message #411179 is a reply to message #411042] Thu, 02 July 2009 05:22 Go to previous message
Supertd
Messages: 15
Registered: December 2008
Location: Africa
Junior Member
Thank you for all the assistance. Smile
Previous Topic: Oracle Analytic Function
Next Topic: Inserting values from lower precision to higher
Goto Forum:
  


Current Time: Sun Dec 04 04:31:24 CST 2016

Total time taken to generate the page: 0.06191 seconds