CODEWITHSUNDEEP
oracle-databaseplsqlcollections
Manjunath D
Manjunath D

Reputation: 53

How to combine multiple collection data of same type into single collection in oracle SQL

In some implementation, I am getting list of collections as a result which are of same type. But I need a single collection as a output which include data from all collections.

Example : I am getting the below result. ( This is Dynamic list. It may of N number of collections)

TYPE_DATA_COLL(TYPE_DATA(1,abc,1001),TYPE_DATA(2,efg,1002),TYPE_DATE(3,xyz,1003)) TYPE_DATA_COLL(TYPE_DATA(8,www,1008),TYPE_DATA(9,qqq,1009)) TYPE_DATA_COLL(TYPE_DATA(4,aaa,1004),TYPE_DATA(5,efg,1005),TYPE_DATE(6,xyz,1006),TYPE_DATA(7,fff,1007))

But I need like

TYPE_DATA_COLL(TYPE_DATA(1,abc,1001),TYPE_DATA(2,efg,1002),TYPE_DATE(3,xyz,1003),TYPE_DATA(8,www,1008),TYPE_DATA(9,qqq,1009),TYPE_DATA(4,aaa,1004),TYPE_DATA(5,efg,1005),TYPE_DATE(6,xyz,1006),TYPE_DATA(7,fff,1007))

Upvotes: 0

Views: 402

Answers (1)

Littlefoot
Littlefoot

Reputation: 143023

To me, it looks as if multiset union would do (see line #8). A simple example:

SQL> declare
  2    type myt is table of number;
  3
  4    l_one myt := myt (1, 3, 5, 7, 9);
  5    l_two myt := myt (0, 2, 4, 6, 8);
  6    l_res myt;
  7  begin
  8    l_res := l_one multiset union l_two;
  9
 10    for i in l_res.first .. l_res.last loop
 11      dbms_output.put_line(l_res(i));
 12    end loop;
 13  end;
 14  /
1
3
5
7
9
0
2
4
6
8

PL/SQL procedure successfully completed.

SQL>

I don't know how you're getting that "dynamic list", but I guess you should be able to do it. If not, consider posting test case (code that returns collections you mentioned) so that we'd see what you have.

Upvotes: 1

Related Questions