Append multiple records from each loop iteration in PL/SQL

Question

I am trying to write a procedure in Oracle PL/SQL that looks like below snippet:

    > LOOP:   
         >READ INPUT FROM CURSOR
         >GET OUTPUTS USING SELECT QUERY(OUTPUT RECORD COUNT MAY VARY ON EACH ITERATION)
         >APPEND OUTPUT TO SOME DATA STRUCTURE 
    > END LOOP; 
    >RETURN DATA STRUCTURE

Actually I am stuck with this appending task. In each iteration different number of output will be available using a select query. I want to append all of the records in a single data structure.

Suppose inner select query return 3 columns ColA,ColB,ColC and the loop iterates 2 times. Then in 1st iteration it returns:

1,2,3
3,4,5

In the next iteration it returns 4,5,7.

The procedure will return some data structure containing below:

1,2,3
3,4,5
4,5,7

Is there any way?

What is the returned data type? array, concatenated varchar, etc? — Hawk
– Hawk, Commented Aug 16, 2016 at 5:02
I am allowed to return anything that holds required set of data. Any composite datatype will do I think. — fuzzy
– fuzzy, Commented Aug 16, 2016 at 5:12

PKey · Accepted Answer · 2016-08-16 05:51:42Z

1

I guess this can be solved with pipelined functions. So it will look something like this:

declare a type

 TYPE my_record is RECORD(
       cola      number,
       colb      number,
       colc       number);

TYPE my_table IS TABLE OF my_record;

a function

function  get_results()  RETURN my_table PIPELINED is 
    my_rec   my_record:=null;
    cursor myCursor(data number) is
    --some select expression
    ;

    begin 

    --loop through outputs 

     FOR item IN myCursor(data)  LOOP
      my_rec:=null; 
      select item.value1,item.value2,item.value2 into rec from dual;
      PIPE ROW(my_rec);
     end loop;
    return;

    end;

and finally use it

SELECT * FROM TABLE(get_results);

answered Aug 16, 2016 at 5:51

PKey

3,8712 gold badges21 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

Append multiple records from each loop iteration in PL/SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related