Home » SQL & PL/SQL » SQL & PL/SQL » Passing Refcursors (Oracle 10g)
Passing Refcursors [message #287497] Wed, 12 December 2007 07:08 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi All

Please help me in the given case.

DECLARE
   TYPE rc IS REF CURSOR;
   erow_out   scott.emp%ROWTYPE;
   rc1        rc;
   PROCEDURE proc (rr sys_refcursor)
   IS
      erow   scott.emp%ROWTYPE;
   BEGIN
   DBMS_OUTPUT.PUT_LINE ( ' in inner proc' );
      LOOP
         FETCH rr
          INTO erow;
		  EXIT WHEN rr%NOTFOUND;
         DBMS_OUTPUT.put_line (erow.ename);
         END LOOP;
   END proc;
BEGIN
   OPEN rc1 FOR
      SELECT *
        FROM scott.emp;
   LOOP
      FETCH rc1
       INTO erow_out;
      EXIT WHEN rc1%NOTFOUND;
	        DBMS_OUTPUT.put_line ('*' || erow_out.ename);
   END LOOP;
   proc (rc1);
END;


In the above example, i am passing a ref cursor into a internal
procedure (proc) of anonymous block. when i try to use the ref cursor records, i am getting no values.

Please help me in this regard

Thanks
Natesh
Re: Passing Refcursors [message #287500 is a reply to message #287497] Wed, 12 December 2007 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already fetch all the records before calling the procedure.
So this one has nothing to fetch.

Regards
Michel
Re: Passing Refcursors [message #287504 is a reply to message #287500] Wed, 12 December 2007 07:20 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hmmmm yeah..

Thanks for the reply Mic..

So before i call the procedure, i have to open the same ref cursor with same select statement.
do we have any other way, as my actual select statement is around 200 lines?

Thanks
Natesh
Re: Passing Refcursors [message #287505 is a reply to message #287504] Wed, 12 December 2007 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to retrieve the same thing again, yes.
If you want to retrieve the rows only in proc, you don't have to fetch them in main, just open the cursor.

Regards
Michel

Re: Passing Refcursors [message #287507 is a reply to message #287505] Wed, 12 December 2007 07:35 Go to previous message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi Mic

Thanks For The Reply

Best Regards
Natesh
Previous Topic: error in utl_file
Next Topic: Need help with a where clause
Goto Forum:
  


Current Time: Thu Dec 08 23:50:34 CST 2016

Total time taken to generate the page: 0.11309 seconds