Archives du mot-clé merge

Merging PL/SQL arrays… or when Oracle is so nice with me

Today i was searching a nice way to merge 2 arrays inside a pl/sql bloc.

I have found plenty of solutions over the web with select statment and cast, or with loop inside PL/SQL.

But in fact there is a more easier way to do that in 1 row … This is possible with the MULTISET operator.

DECLARE
  TYPE t_type IS TABLE OF INTEGER;
  v_t1 t_type;
  v_t2 t_type;
  v_t3 t_type := t_type();
BEGIN
  SELECT * BULK COLLECT
  INTO   v_t1
  FROM   (SELECT 1 FROM dual UNION SELECT 2 FROM dual);
  v_t3 := v_t1 MULTISET UNION ALL v_t3;
  SELECT * BULK COLLECT
  INTO   v_t1
  FROM   (SELECT 3 FROM dual UNION SELECT 4 FROM dual);
  v_t3 := v_t1 MULTISET UNION ALL v_t3;
  FOR i IN v_t3.first .. v_t3.last LOOP
    dbms_output.put_line(v_t3(i));
  END LOOP;
END;
/

Which will display

3
4
1
2