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

Query

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

Query

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

Query

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

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

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;
--                !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--                TYPE fetch_collection_typ IS TABLE OF record_cursor%ROWTYPE;
    fetch_collection_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'fetch_collection_typ' USAGE CHANGED TO VARIANT ***/!!!;
--                !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

--    TYPE collection_typ IS TABLE OF bulk_collect_table%ROWTYPE;
    collection_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'collection_typ' USAGE CHANGED TO VARIANT ***/!!!;
    BEGIN
                !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'RECORDS AND COLLECTIONS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
                SELECT * BULK COLLECT INTO collection_variable FROM bulk_collect_table;
                FOR i IN 1 TO 0 /*collection_variable.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE CUSTOM TYPE EXPRESSION' NODE ***/!!! LOOP
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            CALL DBMS_OUTPUT.PUT_LINE_UDF(:collection_variable(i).col1);
                END LOOP;
                !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

                collection_variable := null;
                OPEN record_cursor;
                --** SSC-PRF-0001 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
                record_cursor := (
            CALL FETCH_BULK_COLLECTION_RECORDS_UDF(:record_cursor)
                );
                collection_variable := :record_cursor:RESULT;
                CLOSE record_cursor;
                FOR i IN 1 TO 0 /*collection_variable.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE CUSTOM TYPE EXPRESSION' NODE ***/!!! LOOP
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            CALL DBMS_OUTPUT.PUT_LINE_UDF(
            !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!
            :collection_variable(i).col1+6);
                END LOOP;
                !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

                collection_variable := null;
                !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
                EXECUTE IMMEDIATE 'SELECT * FROM
   bulk_collect_table'
                      !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'EXECUTE IMMEDIATE RETURNING CLAUSE' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
                      BULK COLLECT INTO collection_variable;
                FOR i IN 1 TO 0 /*collection_variable.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE CUSTOM TYPE EXPRESSION' NODE ***/!!! LOOP
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            CALL DBMS_OUTPUT.PUT_LINE_UDF(
            !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!
            :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
    -- 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:

WITH, 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. SSC-EWI-0058: Functionality is not currently supported by Snowflake Scripting.
2. SSC-EWI-0062: Custom type usage changed to variant.
3. SSC-EWI-0073: Pending Functional Equivalence Review
4. SSC-EWI-OR0036: Types resolution issues, the arithmetic operation may not behave correctly between string and date.
5. SSC-EWI-OR0108: The Following Assignment Statement is Not Supported by Snowflake Scripting.
6. SSC-FDM-OR0035: DBMS_OUTPUT.PUTLINE check UDF implementation.
7. SSC-PRF-0001: This statement has usages of cursor fetch bulk operations.
8. SSC-EWI-0030: The statement below has usages of dynamic SQL