What is BULK COLLECT in Oracle?

PL/SQL statements are run by the PL/SQL statement executor; SQL statements are run by the SQL statement executor. When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine. This transfer of control is called a Context Switch, and each one of these switches incurs overhead that slows down the overall performance of your programs.

Generally, the way to improve performance over row-by-row context switching is to not perform row-by-row DML operations. This can be accomplished in one of two ways:

1. Implement the functionality in “pure” SQL – no PL/SQL loop.
2. Use the Bulk processing features of PL/SQL.

Equivalent feature in PostgreSQL

PostgreSQL does not have Bulk collect feature, but you can leverage PostgreSQL array_agg() function feature to achieve bulk collect functionality of Oracle. The PostgreSQL array_agg() function is an aggregate function that accepts a set of values and returns an array in which each value in the set is assigned to an element of the array.

Syntax:
ARRAY_AGG(expression [ORDER BY [sort_expression {ASC | DESC}], [...])

BULK COLLECT Example

Let’s create a sample table and insert some data.

--Oracle
create table test_bulk_c as 
SELECT LEVEL as id, 'test_'||LEVEL as name, sysdate+LEVEL as tdate
FROM DUAL
CONNECT BY LEVEL < 101;

SELECT COUNT(1) FROM test_bulk_c;


--PostgreSQL
create table test_bulk_c as 
SELECT i::INTEGER as id, 'test_'||i as name, current_date+i as tdate
FROM generate_series(1, 100) i;

SELECT COUNT(1) FROM test_bulk_c;

We will explore on following Bulk Collect scenarios.

1. BULK COLLECT to ARRAY_AGG For Single Column
2. BULK COLLECT to ARRAY_AGG For Multiple Columns

Converting BULK COLLECT to ARRAY_AGG for Single Column

Converting BULK COLLECT to ARRAY_AGG For Multiple Columns

SET SERVEROUTPUT ON;

DECLARE

    TYPE bulkc_tbl IS TABLE OF VARCHAR2(65) INDEX BY PLS_INTEGER;

    v_bulkc    bulkc_tbl;
BEGIN


    SELECT name

    BULK COLLECT INTO v_bulkc

    FROM test_bulk_c;

    DBMS_OUTPUT.PUT_LINE('Total rows count: ' || v_bulkc.count);


  
  FOR i IN v_bulkc.first.. v_bulkc.last LOOP
        dbms_output.put_line('NAME: '|| v_bulkc(i));
    END LOOP;

END;
/

Output:
Total rows count: 100
NAME: test_1
NAME: test_2
NAME: test_3
NAME: test_4
…

PL/SQL procedure successfully completed.

SET client_min_messages = debug;


DO $$

DECLARE

    v_bulkc  CHARACTER VARYING(65)[];

BEGIN

    SELECT array_agg(name) INTO v_bulkc 
    FROM test_bulk_c;
    
    raise debug 'Total rows count: %', coalesce(array_length(v_bulkc, 1), 0);



    FOR i in 1 .. coalesce(array_length(v_bulkc, 1), 0) LOOP

        raise debug using message := concat_ws('', 'NAME: ', v_bulkc[i]);

    END LOOP;

END $$;


Output:
DEBUG:  Total rows count: 100
DEBUG:  NAME: test_1
DEBUG:  NAME: test_2
DEBUG:  NAME: test_3
DEBUG:  NAME: test_4
…

Common errors while migrating

Do not use ROW operator while migrating single column array_agg operation.

SET client_min_messages = debug;


DO
$$
DECLARE

    v_bulkc  CHARACTER VARYING(65)[];

BEGIN

    --Using ROW operator with single column array_agg leads to incorrect output values
    SELECT array_agg(ROW(name)) 
    INTO v_bulkc 
    FROM test_bulk_c;    
    
    raise debug 'Total rows count: %', coalesce(array_length(v_bulkc, 1), 0);


  
    FOR i in 1 .. coalesce(array_length(v_bulkc, 1), 0) LOOP

        raise debug using message := concat_ws('', 'NAME: ', v_bulkc[i]);

    END LOOP;

END $$;


Output:
DEBUG:  Total rows count: 100
DEBUG:  NAME: (test_1)
DEBUG:  NAME: (test_2)
DEBUG:  NAME: (test_3)
DEBUG:  NAME: (test_4)
...

Notice here, the values fetched from Array comes with a bracket which is not correct as in original table we have value without brackets.

You can use * to specify all columns rather than putting all column names during multi-column array_agg operation. * has to be used with table prefix, else it throws error.

DO $$
DECLARE
    v_rec_bulkc  test_bulk_c[];

BEGIN
    --Using * to specify all columns, but without table prefix
    SELECT array_agg(ROW(*)) into v_rec_bulkc FROM test_bulk_c;    raise debug 'Total rows count: %', coalesce(array_length(v_rec_bulkc, 1), 0);
    
    FOR i in 1 .. coalesce(array_length(v_rec_bulkc, 1), 0) LOOP
        raise debug using message := concat_ws('', 'ID: '|| v_rec_bulkc[i].id ||', NAME: '|| v_rec_bulkc[i].name ||', Date: '|| v_rec_bulkc[i].tdate);
    END LOOP;
END $$;

ERROR:  syntax error at or near "*"
LINE 6:     SELECT array_agg(ROW(*)) into v_rec_bulkc FROM test_bulk...

Correct way to use * with array_agg

DO $$
DECLARE
    v_rec_bulkc  test_bulk_c[];

BEGIN
    SELECT array_agg(ROW(t.*))
    INTO v_rec_bulkc
    FROM test_bulk_c t;
    raise debug 'Total rows count: %', coalesce(array_length(v_rec_bulkc, 1), 0);
  
    FOR i in 1 .. coalesce(array_length(v_rec_bulkc, 1), 0) LOOP
        raise debug using message := concat_ws('', 'ID: '|| v_rec_bulkc[i].id ||', NAME: '|| v_rec_bulkc[i].name ||', Date: '|| v_rec_bulkc[i].tdate);
    END LOOP;
END $$;