Andrew Fraser DBA

PL/SQL bulk collect union

PL/SQL bulk collect will work with UNION ALL when written with an inline view:

DECLARE
   TYPE l_type_assoc IS TABLE OF VARCHAR2(14) INDEX BY BINARY_INTEGER ;
   l_big l_type_assoc ;
BEGIN
     WITH q AS (
          SELECT ename AS name
            FROM emp
           UNION ALL
          SELECT dname AS name
            FROM dept
          )
   SELECT name
     BULK COLLECT INTO l_big
     FROM q
    ORDER BY name ;

   FOR indx IN 1..l_big.COUNT
   LOOP
      DBMS_OUTPUT.put_line ( l_big(indx) ) ;
   END LOOP ;

END ;
/

If the array you are using is not an associative array (that is, does not have the INDEX BY clause in its declaration) then an alternative method is to merge arrays together after they are populated with multiset union:

DECLARE
   TYPE l_type_varray IS TABLE OF VARCHAR2(14) ;
   l_tmp l_type_varray ;
   l_big l_type_varray ;
BEGIN

   SELECT ename BULK COLLECT INTO l_tmp FROM emp ;

   l_big := l_tmp ;  -- copy the array

   SELECT dname BULK COLLECT INTO l_tmp FROM dept ;

   l_big := l_big MULTISET UNION ALL l_tmp ;  -- union the arrays

   FOR indx IN 1..l_big.COUNT
   LOOP
      DBMS_OUTPUT.put_line ( l_big(indx) ) ;
   END LOOP ;

END ;
/

PHP’s oci_bind_array_by_name will only work with associative arrays, returns error ‘ORA-06550: PLS-00306: wrong number or types of arguments in call’ if varrays are used.