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
statementWith 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
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
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
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.
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;
fetch_collection_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'fetch_collection_typ' USAGE CHANGED TO VARIANT ***/!!!;
-- --** SSC-FDM-0024 - 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
-- --** SSC-FDM-0024 - 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;
EXECUTE IMMEDIATE 'SELECT * FROM
bulk_collect_table'
-- --** SSC-FDM-0024 - 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();
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 statementsKnown 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
SSC-EWI-0020: CUSTOM UDF INSERTED.
SSC-FDM-0024: Functionality is not currently supported by Snowflake Scripting.
SSC-EWI-0062: Custom type usage changed to variant.
SSC-EWI-0073: Pending Functional Equivalence Review
SSC-EWI-OR0036: Types resolution issues, the arithmetic operation may not behave correctly between string and date.
SSC-EWI-OR0108: The Following Assignment Statement is Not Supported by Snowflake Scripting.
SSC-EWI-OR0115: CHECK UDF IMPLEMENTATION.
SSC-PRF-0001: This statement has usages of cursor fetch bulk operations.
Last updated