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 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
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 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;
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
MSCEWI1058: Functionality is not currently supported by Snowflake Scripting.
Last updated
Was this helpful?