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;

Dessma · Answer 1 · 2017-09-05T19:07:40.023

To loop through a ref cursor is not like looping through an array or table which explains why your FOR...LOOP is not working.

In short, instead of a collection, the ref_cursor is more of a "pointer" or an "iterator" over a collection. In this other question you will find a quite clear example of iterating through a ref_cursor using FETCH.

How to use record to loop a ref cursor?

An example with your data would look like this :

PROCEDURE GET_ORDINARY_CLIENT_BY_SED(sed_in     IN VARCHAR2,
                                     client_sed OUT SYS_REFCURSOR) IS
   ordinary_clients SYS_REFCURSOR;
   clt              NUMBER; -- assuming your cursor contains strictly numbers
BEGIN
   ordinary_clients := GET_CLIENT_IN_SED(sed_in);
   LOOP
      FETCH ordinary_clients
         INTO clt;
      EXIT WHEN ordinary_clients%NOTFOUND;
      dbms_output.put_line(clt);
      -- do some other things here with your number
   END LOOP;
END;

I am not trying to load from a table, I am trying to load from a sys_refcursor return function, because i have no values stored in any table, just instructions about how to obtain the values. So I cant use table_name%ROWTYPE — miMescua, Sep 05 '17 at 18:52
Sorry I assumed your `ordinary_client` cursor contained a "complex" type such as a table row. If your refcursor only contains simple data such as numbers, you can FETCH into a simple variable of the appropriate type and use them with the same logic. I will edit my answer with additional info. — Dessma, Sep 05 '17 at 19:05

score 1 · Accepted Answer · answered Sep 06 '17 at 11:45

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;

Bulk collect is another valid approach that I liked to use (I find it much more simple to work with a collection than with a cursor!). Although better not to be used when the amount of data is very large because it stores in a variable instead of an iterator. — Dessma, Sep 06 '17 at 13:39
@Dessma You can iterate using bulk collect as well. You have limit clause to restrict data selection in 1 go and you can iterate thorugh all records — XING, Sep 06 '17 at 13:41

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

2 Answers2