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;

XING · Accepted Answer · 2017-09-06 11:45:33Z

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;

answered Sep 6, 2017 at 11:45

XING

9,9264 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dessma Over a year ago

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.

XING Over a year ago

@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

Dessma · Accepted Answer · 2017-09-05 19:07:40Z

1

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;

edited Sep 5, 2017 at 19:07

answered Sep 5, 2017 at 18:01

Dessma

6093 silver badges11 bronze badges

2 Comments

miMescua Over a year ago

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

Dessma Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related