Oracle PL/SQL Assign each value from a cursor(from function) to another cursor one by one

Question

I have a function called GET_CLIENT_IN_SED(return sys_refcursor), it gives me a list of id numbers(single column). Now, in a procedure, I am trying to loop through each (one by one) of that values and use it for calling a second procedure (it needs a client id parameter).

PROCEDURE GET_ORDINARY_CLIENT;

PROCEDURE GET_ORDINARY_CLIENT_BY_SED
  ( sed_in IN varchar2, client_sed OUT SYS_REFCURSOR )
IS
   ordinary_clients sys_refcursor;

BEGIN
  ordinary_clients := GET_CLIENT_IN_SED(sed_in);
  for item in ordinary_clients loop
    client_sed := client_sed + ordinary_clients(i);
  end loop;
END;

Answer 1

As far i could understand you need to do something like :

Function:

This function would take input as number and return a refcursor. Similar to your requirement.

CREATE OR REPLACE FUNCTION get_num_sysrefcur (num IN NUMBER)
   RETURN SYS_REFCURSOR
AS
   my_cursor   SYS_REFCURSOR;
BEGIN
   --
   OPEN my_cursor FOR
      WITH ntable
           AS (SELECT 1 ID, 111 AGT, 'ABC' DESCRIP FROM DUAL
               UNION ALL
               SELECT 2 ID, 222 AGT, 'ABC' DESCRIP FROM DUAL
               UNION ALL
               SELECT 1 ID, 333 AGT, 'ABC' DESCRIP FROM DUAL)

         SELECT AGT FROM ntable WHERE ID = num;


   RETURN my_cursor;
END;
/

Block ( In your case Procedure ) -- This anonymous block will loop through the records return from the sys_refcursor. Similiar to you need where you want the second procedure to use the value of sys_refcursor and loop it(You can create procedure in place of this anonymous block).

DECLARE
   a   NUMBER := 1;

   TYPE ta IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER;
   b   ta;
   x   SYS_REFCURSOR;
BEGIN
   x := get_num_sysrefcur (a);

  fetch x bulk collect into b;

  for i in 1..b.count
  loop
   -- Displaying the result of the ref_cursor.
   DBMS_OUTPUT.put_line (b(i));
  end loop;
END;