Collection Bulk Operations

This is a translation reference to convert the Oracle Collection Bulk Operations to Snowflake

This section is a work in progress, information may change in the future

Description

The BULK COLLECT clause, a feature of bulk SQL, returns results from SQL to PL/SQL in batches rather than one at a time.

The BULK COLLECT clause can appear in:

• SELECT INTO statement

• FETCH statement

• RETURNING INTO clause of:

  • DELETE statement

  • INSERT statement

  • UPDATE statement

  • EXECUTE IMMEDIATE statement

With the BULK COLLECT clause, each of the preceding statements retrieves an entire result set and stores it in one or more collection variables in a single operation (which is more efficient than using a loop statement to retrieve one result row at a time).

(Oracle PL/SQL Language Reference BULK COLLECT CLAUSE)

This section has some workarounds for SELECTs and FETCH Cursor with Bulk Clauses.

Sample Source Patterns

Source Table

Oracle

Query

CREATE TABLE bulk_collect_table(col1 INTEGER);

INSERT INTO bulk_collect_table VALUES(1);
INSERT INTO bulk_collect_table VALUES(2);
INSERT INTO bulk_collect_table VALUES(3);
INSERT INTO bulk_collect_table VALUES(4);
INSERT INTO bulk_collect_table VALUES(5);
INSERT INTO bulk_collect_table VALUES(6);

Snowflake

Query

CREATE OR REPLACE TABLE PUBLIC.bulk_collect_table (col1 INTEGER);

INSERT INTO PUBLIC.bulk_collect_table
VALUES(1);
INSERT INTO PUBLIC.bulk_collect_table
VALUES(2);
INSERT INTO PUBLIC.bulk_collect_table
VALUES(3);
INSERT INTO PUBLIC.bulk_collect_table
VALUES(4);
INSERT INTO PUBLIC.bulk_collect_table
VALUES(5);
INSERT INTO PUBLIC.bulk_collect_table
VALUES(6);

Bulk Collect from a Table

Oracle

Query

CREATE OR REPLACE PROCEDURE bulk_collect_procedure
IS
    CURSOR record_cursor IS
        SELECT *
        FROM bulk_collect_table;
        
    TYPE fetch_collection_typ IS TABLE OF record_cursor%ROWTYPE;
    fetch_collection_variable fetch_collection_typ;
    
    TYPE collection_typ IS TABLE OF bulk_collect_table%ROWTYPE;
    collection_variable collection_typ;
BEGIN
    SELECT * BULK COLLECT INTO collection_variable FROM bulk_collect_table;
    
    FOR i IN 1..collection_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(collection_variable(i).col1);
    END LOOP;
    
    collection_variable := null;
    OPEN record_cursor;
    FETCH record_cursor BULK COLLECT INTO collection_variable;
    CLOSE record_cursor;
    
    FOR i IN 1..collection_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(collection_variable(i).col1+6);
    END LOOP;
    
    collection_variable := null;
    EXECUTE IMMEDIATE 'SELECT * FROM bulk_collect_table' BULK COLLECT INTO collection_variable;
    
    FOR i IN 1..collection_variable.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(collection_variable(i).col1+12);
    END LOOP;
END;
/

CALL bulk_collect_procedure();

Result

DBMS OUTPUT
-----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Snowflake

EXECUTE IMMEDIATE with Bulk Collect clause has no workarounds offered.

Please note, that while the FETCH Cursor can be mostly preserved, it is advised to be changed into SELECT statements whenever possible for performance issues.

Query

CREATE OR REPLACE PROCEDURE PUBLIC.bulk_collect_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    fetch_collection_variable ARRAY;
    collection_variable ARRAY;
BEGIN
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*)) INTO collection_variable FROM bulk_collect_table; 
    
    FOR i IN 1 TO ARRAY_SIZE(collection_variable)
    LOOP
      CALL DBMS_OUTPUT.PUT_LINE(:collection_variable[:i-1]:COL1);
    END LOOP;
    
    collection_variable := null;
    CREATE OR REPLACE TEMPORARY TABLE record_cursor AS SELECT seq8() index, t.* FROM bulk_collect_table t;
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*)) INTO fetch_collection_variable FROM record_cursor; 
    
    FOR i IN 1 TO ARRAY_SIZE(fetch_collection_variable)
    LOOP
      CALL DBMS_OUTPUT.PUT_LINE(:fetch_collection_variable[:i-1]:COL1+6);
    END LOOP;
    
    collection_variable := null;
    --NO WORKAROUND OFFERED
    /*
    EXECUTE IMMEDIATE 'SELECT * FROM bulk_collect_table' BULK COLLECT INTO collection_variable;
    
    FOR i IN 1 TO ARRAY_SIZE(collection_variable)
    LOOP
      CALL DBMS_OUTPUT.PUT_LINE(:collection_variable[:i-1]:COL1+12);
    END LOOP;
    */
END;
$$;

CALL bulk_collect_procedure();
SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

Result

DBMS OUTPUT
-----------
1
2
3
4
5
6
7
8
9
10
11
    -- EXECUTE IMMEDIATE NOT EXECUTED, it's not supported

SELECT INTO statement case

In this case, the translation specification uses RESULTSETs. Review the documentation for WITH, SELECT, and BULK COLLECT INTO statements here:

pageWITH, SELECT, and BULK COLLECT INTO statements

Known Issues

1. Heavy performance issues on FETCH Cursor workaround

The workaround for the Fetch cursor has heavy performance requirements due to the Temporary table. It is advised for them to be manually migrated to SELECT statements

2. Execute immediate statements are not transformed

They are not supported by SnowConvert but may be manually changed to SELECT statements.

Related EWIs

1. MSCEWI1058: Functionality is not currently supported by Snowflake Scripting.