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

Some parts in the output code are omitted for clarity reasons.

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

IN -> Oracle_01.sql
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

OUT -> Oracle_01.sql
CREATE OR REPLACE TABLE bulk_collect_table (col1 INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

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);

Bulk Collect from a Table

Oracle

IN -> Oracle_02.sql
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();

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.

OUT -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE bulk_collect_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        record_cursor CURSOR
        FOR
            SELECT *
            FROM
                bulk_collect_table;
--                --** SSC-FDM-0024 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--                TYPE fetch_collection_typ IS TABLE OF record_cursor%ROWTYPE;