Merge collections in oracle?

Question

I need to merge two collections. I know two tables can be merged , but not sure of collections. What is the best way to merge collections ? Say below is the sample code

------------
CREATE OR REPLACE TYPE obj_test AS OBJECT(
id number(9),
val number (9)
)
/

CREATE OR REPLACE TYPE obj_test_list AS TABLE OF obj_test
/

I have two lists/collections

list1 obj_test_list ;
list2 obj_test_list ;
list3 obj_test_list ;


list1        
id val 
1 100 
2 200 
3 300


list2       
id val 
1 300 
4 500 

I want to match list1 and list2 based on id and add val else insert. I want it in list3 as follows.

list3 
id val 
1 400 
2 200 
3 300
4 500

Can someone give a sample code for this?

Answer 1

Using the TABLE() operator, you can manipulate your collections using SQL statements just like if they were DB tables. In your case, for example, to perform a FULL OUTER JOIN:

SELECT obj_test(id,NVL(T1.val,0)+NVL(T2.val,0))
       BULK COLLECT INTO list3
       FROM TABLE(list1) T1 FULL OUTER JOIN TABLE(list2) T2 USING(id);

Given your two sample list, this will store in list3:

1 400
2 200
3 300
4 500

---

Full code to test:

DECLARE

    list1 obj_test_list := obj_test_list(obj_test(1,100),obj_test(2,200),obj_test(3,300));
    list2 obj_test_list := obj_test_list(obj_test(1,300),obj_test(4,500));
    list3 obj_test_list;
    indx  PLS_INTEGER;

BEGIN

    SELECT obj_test(id,NVL(T1.val,0)+NVL(T2.val,0))
           BULK COLLECT INTO list3
           FROM TABLE(list1) T1 FULL OUTER JOIN TABLE(list2) T2 USING(id);

    indx := list3.FIRST;
    WHILE(indx IS NOT NULL)
    LOOP
      DBMS_OUTPUT.PUT(list3(indx).id);
      DBMS_OUTPUT.PUT(' ');
      DBMS_OUTPUT.PUT_LINE(list3(indx).val);
      indx := list3.NEXT(indx);
    END LOOP;

END;

Answer 2

Collections can be merged with SQL. Create the collections, convert the collections into tables, join the tables, and then convert the tables back into a collection.

It can be tricky the first time you encounter this inside-out inline view logic flow. Especially with advanced features like object types, cross joins, and cast/collect. The steps are numbered and lettered to help you keep track. The advantage to building a query this way is that it's much easier to debug. Start from the middle, highlight and run a query block in your IDE, and keep moving out until you understand the whole query.

--#4: Create new collection of results.
select cast(collect(obj_test(id, val)) as obj_test_list)
from
(
  --#3: Join lists and add results - returns results in normalized format.
  select
    coalesce(list_1_normalized.id, list_2_normalized.id) id,
    coalesce(list_1_normalized.val, 0) + coalesce(list_2_normalized.val, 0) val
  from
  (
    --#2a: List 1 normalized.
    select id, val
    from
    (
      --#1a: List 1 objects.
      select obj_test_list(obj_test(1,100),obj_test(2,200),obj_test(3,300))list
      from dual
    ) list_1_objects
    cross join table(list_1_objects.list)
  ) list_1_normalized
  full outer join
  (
    --#2b: List 2 normalized.
    select id, val
    from
    (
      --#1b: List 2 objects.
      select obj_test_list(obj_test(1,300),obj_test(4,500))list
      from dual
    ) list_2_objects
    cross join table(list_2_objects.list)
  ) list_2_normalized
    on list_1_normalized.id = list_2_normalized.id
);