How to store result of a function which will return sysrefcursor?

Question

Scenario: there is an procedure inside which we have a cursor. I need to call a function which will take an input from that cursor value and will return SYS_REFCURSOR.

I need to store that result of function in a different variable/cursor & need to return this value from procedure as out parameter.

I am using Oracle 11g.

How can I proceed?

PFB My Approach:

create or replace procedure prc_test 
    (p_dept_id in number,
    c_detail out sysrefcursor)--need to add extra out parameter
as
     var1 varchar2(200) :=null;

begin
   open c_detail for
     select -1 from dual;
   if p_dept_id is not null then
     open c_detail for
         select emp_no from emp 
          where dept_id=p_dept_id;     
     --i need to retrn value of  'get_emp_dtls' function as out parameter.
   end if;

end procedure;
/

Function to be called

CREATE OR REPLACE FUNCTION get_emp_dtls
    (p_emp_no IN EMP.EMP_NO%TYPE)
   RETURN SYS_REFCURSOR  
AS
   o_cursor   SYS_REFCURSOR;   
BEGIN
   OPEN o_cursor FOR 
      SELECT 
         ENAME,
         JOB            
       FROM emp
       WHERE EMP_NO = p_emp_no;   
   RETURN o_cursor;
   -- exception part
END;
/

Answer 1

Here is your function that takes a varchar2 variable and returns A refcursor( weakly typed).

 CREATE OR replace FUNCTION fn_return_cur(v IN VARCHAR2)
RETURN SYS_REFCURSOR
IS
  c1 SYS_REFCURSOR;
BEGIN
    OPEN c1 FOR
      SELECT 'ABC'
      FROM   dual
      WHERE  'col1' = v;

    RETURN c1;
END;

/  

Here is the procedure that has a cursor value passed as argument to function and the returned cursor passed as OUT argument.

 CREATE OR replace PROCEDURE Pr_pass_out_cur(v_2 OUT SYS_REFCURSOR)
IS
  func_arg  VARCHAR2(3);
  other_arg VARCHAR2(3);
  CURSOR c_2 IS
    SELECT 'ABC' col1,
           'DEF' col2
    FROM   dual;
BEGIN
    LOOP
        FETCH c_2 INTO func_arg, other_arg;

        EXIT WHEN c_2%NOTFOUND;

        v_2 := Fn_return_cur(func_arg);
    END LOOP;
EXCEPTION
  WHEN OTHERS THEN
             NULL;
END;

/  

Let me know your feedback.