Home » SQL & PL/SQL » SQL & PL/SQL » How to send refcursor result to a procedure as in parameter to get result (Oracle, 9.2.0.5,XP)
How to send refcursor result to a procedure as in parameter to get result [message #401739] Wed, 06 May 2009 05:32 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Create or Replace Procedure A
( in_emp_id number,
   RC SYS_REFCURSOR)
is
Begin
 OPEN RC for
 select emp_name, dept_id,sal from EMP where emp_id=in_emp_id;
end;


Now, I want to pass values returned by refcursor to procedure b to get result.
Sir/Madam, How I can pass ref cursor values to a procedure which accepts the values as a parameter?
Any simple example would help to understand.

[Updated on: Wed, 06 May 2009 05:36]

Report message to a moderator

Re: How to send refcursor result to a procedure as in parameter to get result [message #401749 is a reply to message #401739] Wed, 06 May 2009 06:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to do a FETCH <cursor name> INTO <variable list> to get the values back frmo the cursor. Then you just pass them to the procedure as you ould normally.

Alternatively, you can pass the Ref Cursor into the procedure (assuming it can take such a parameter) and the procedure can loop through the cursor and do what it likes with the data.
Re: How to send refcursor result to a procedure as in parameter to get result [message #401753 is a reply to message #401749] Wed, 06 May 2009 06:18 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Create or Replace Procedure A
( in_emp_id number,
   RC SYS_REFCURSOR)
Begin
 OPEN RC for
 select emp_name, dept_id,sal from EMP where emp_id=in_emp_id;
end;

I need to Pass the values returned from refcursor to another existing procedure
B ( in_emp_nm, in dept_id, in_sal, RC1 out sys_refcursor).




The above is just the pseudo ( not the original code). Could anyone give me a simple
demo to make me undersatnd for the above case?

[Updated on: Wed, 06 May 2009 06:19]

Report message to a moderator

Re: How to send refcursor result to a procedure as in parameter to get result [message #401754 is a reply to message #401739] Wed, 06 May 2009 06:20 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

Alternatively, you can pass the Ref Cursor into the procedure (assuming it can take such a parameter) and the procedure can loop through the cursor and do what it likes with the data.


--- Sir, I cant make any modification to procedure B.
Re: How to send refcursor result to a procedure as in parameter to get result [message #401757 is a reply to message #401754] Wed, 06 May 2009 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think JRowbottom's answer is a clear and complete solution of what you need, given the fact that you can't modify procedure B:
Quote:
You need to do a FETCH <cursor name> INTO <variable list> to get the values back frmo the cursor. Then you just pass them to the procedure as you ould normally.

But you have to know that this is a slow process.

Regards
Michel
Re: How to send refcursor result to a procedure as in parameter to get result [message #401762 is a reply to message #401757] Wed, 06 May 2009 06:43 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
How can we use FETCH for refcursor?

Do I need to use RECORD TYPE and then call the procedure A(in_emp_id ,:RC)
and then fETCH bULK COLLECT INTO
and then use for loop to pass each values to procedure B?

[Updated on: Wed, 06 May 2009 07:13]

Report message to a moderator

Re: How to send refcursor result to a procedure as in parameter to get result [message #401769 is a reply to message #401762] Wed, 06 May 2009 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand in:
Quote:
FETCH <cursor name> INTO <variable list>

Regards
Michel
Re: How to send refcursor result to a procedure as in parameter to get result [message #401777 is a reply to message #401762] Wed, 06 May 2009 07:20 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hope the sample example below will help you.You need to modify the procedure how ever as per your requirement.This example will give you an idea.

SQL> select * from sample;

         I          J
---------- ----------
        10         20
        11         21
        12         22

SQL> create or replace procedure a ( i in number , RC out sys_refcursor)
  2  is
  3  begin
  4  open RC for select * from sample;
  5  end;
  6  /

Procedure created.


SQL> ed
Wrote file afiedt.buf

  1   create or replace procedure b
  2   is
  3   rc1 sys_refcursor;
  4   v1 number(20);
  5   v2 number(10);
  6   begin
  7   a(10,rc1);
  8   loop
  9   fetch rc1 into v1,v2;
 10   exit when rc1%notfound;
 11   dbms_output.put_line('value1 -> ' || v1 ||' Value2 -> ' || v2);
 12   end loop;
 13*  end;
SQL> /

Procedure created.

SQL> exec b;
value1 -> 10 Value2 -> 20
value1 -> 11 Value2 -> 21
value1 -> 12 Value2 -> 22

PL/SQL procedure successfully completed.

[Updated on: Wed, 06 May 2009 07:24]

Report message to a moderator

Re: How to send refcursor result to a procedure as in parameter to get result [message #401784 is a reply to message #401777] Wed, 06 May 2009 07:25 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks for the help but I can modify procedure B with refcursor as in parameter.
Re: How to send refcursor result to a procedure as in parameter to get result [message #401789 is a reply to message #401739] Wed, 06 May 2009 07:36 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Create or Replace Procedure A
( in_emp_id number,
   RC SYS_REFCURSOR)
Begin
 OPEN RC for
 select emp_name, dept_id,sal from EMP where emp_id=in_emp_id;
end;


Create or Replace Procedure b
( in_emp_nm number,in_dept_id number, in_sal number,
   RC1 SYS_REFCURSOR)
Begin
.....
 OPEN RC1 for
......
end;



In main procedure what I want is that by passing emp id, the refcursor output values that is obtained from procedure A, I want to feed
to procedure B and then get the ouput and do some processing on output values

[Updated on: Wed, 06 May 2009 07:49]

Report message to a moderator

Re: How to send refcursor result to a procedure as in parameter to get result [message #401801 is a reply to message #401784] Wed, 06 May 2009 08:23 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks for the help but I can NOT modify procedure B with refcursor as in parameter.
Re: How to send refcursor result to a procedure as in parameter to get result [message #401810 is a reply to message #401789] Wed, 06 May 2009 08:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So just do that (You'll need to alter Procedure A to return the Ref Cursor rather than accepting it as a parameter).
(untested)
declare
   c_ref1   sys_refcursor;
   c_ref2   sys_refcursor;
   v_emp    number;
   v_ename  varchar2(100)
   v_dept   number;
   v_sal    number;
begin

   procedure_a(1,c_ref1);
   fetch c_ref1 into v_ename,v_dept,v_sal;

   procedure_b(1,v_dept,v_sal,c_ref2);
end;
/
Re: How to send refcursor result to a procedure as in parameter to get result [message #401813 is a reply to message #401789] Wed, 06 May 2009 08:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Where in panyam's example do you see an OPEN rc in procedure B?
So WHY do you insist on having that in your procedure B?

Advise: learn basic PL/SQL before trying to do the advanced stuff. If you cannot translate a procedure where a local variable is used to one where a parameter is used, you are NOT ready for this.
Re: How to send refcursor result to a procedure as in parameter to get result [message #401814 is a reply to message #401810] Wed, 06 May 2009 08:55 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
thank you..
Re: How to send refcursor result to a procedure as in parameter to get result [message #402015 is a reply to message #401739] Thu, 07 May 2009 05:31 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Create or Replace Procedure proc_c
( in_sal number,
   RC out SYS_REFCURSOR)
is
Begin
 OPEN RC for
 select distinct department_id from employees where salary > in_sal order by department_id;
end;

Create or Replace Procedure proc_d
( in_DEPT_id number,
   RC2 out SYS_REFCURSOR)
is
Begin
 OPEN RC2 for
 select DEPARTMENT_NAME from departments where DEPARTMENT_ID=in_dept_id;
end;

declare
c sys_refcursor;
c1 sys_refcursor;
v1 number;
--v2 varchar2(20);
--v3 number;
begin
proc_c(5000,c);
loop
  fetch c into v1;
  exit when c%notfound;
   dbms_output.put_line( v1 );
end loop;

/* proc_d (v1,c1);
loop
  fetch c1 into v2;
   exit when c1%notfound;
 dbms_output.put_line('----------------------');
   dbms_output.put_line( to_char(v2) );
end loop;*/
end;

Result:
20
30
40
50
60
70
80
90
100
110

I want to pass the values I got from 1st procedure proc_a to procedure proc_b.
I tried this way but returning one department name only.

declare
c sys_refcursor;
c1 sys_refcursor;
v1 number;
v2 varchar2(30);
--v3 number;
begin
proc_c(5000,c);
--loop
  fetch c into v1;
  --exit when c%notfound;
   --dbms_output.put_line( v1 );
--end loop;

proc_d (v1,c1);
loop
  fetch c1 into v2;
   exit when c1%notfound;
 dbms_output.put_line('----------------------');
   dbms_output.put_line( to_char(v2) );
end loop;
end;

Previous Topic: single-row subquery returns more than one row [merged]
Next Topic: Day Number to Full Day Name Conversion
Goto Forum:
  


Current Time: Fri Dec 02 12:21:20 CST 2016

Total time taken to generate the page: 0.06780 seconds