Home » SQL & PL/SQL » SQL & PL/SQL » how to pass multiple values to another procedure (oracle 9.2.0.3)
how to pass multiple values to another procedure [message #402028] Thu, 07 May 2009 06:30 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I am not being able to figure out how to pass multiple values returned from one procedure through refcursor to another procedure.
Here's what I have done so far.

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;

Re: how to pass multiple values to another procedure [message #402043 is a reply to message #402028] Thu, 07 May 2009 07:15 Go to previous message
somdeep2k9
Messages: 4
Registered: April 2009
Junior Member
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 );*/
proc_d (v1,c1);
fetch c1 into v2;
exit when c1%notfound;
dbms_output.put_line( to_char(v2) );
end loop;
end;


[Updated on: Fri, 08 May 2009 03:46]

Report message to a moderator

Previous Topic: query
Next Topic: Converting xmltype to varchar2(merged)
Goto Forum:
  


Current Time: Sun Dec 11 08:02:43 CST 2016

Total time taken to generate the page: 0.06867 seconds