LOG ERROR
Description
The
FORALL
statement runs one DML statement multiple times, with different values in theVALUES
andWHERE
clauses. (Oracle PL/SQL Language Reference FORALL Statement).
FORALL index IN bounds_clause [ SAVE ] [ EXCEPTIONS ] dml_statement ;
Snowflake Scripting has no direct equivalence with the FORALL
statement, however can be emulated with different workarounds to get functional equivalence.
Sample Source Patterns
Setup Data
Oracle
CREATE TABLE error_table (
ORA_ERR_NUMBER$ NUMBER,
ORA_ERR_MESG$ VARCHAR2(2000),
ORA_ERR_ROWID$ ROWID,
ORA_ERR_OPTYP$ VARCHAR2(2),
ORA_ERR_TAG$ VARCHAR2(2000)
);
--departments
CREATE TABLE parent_table(
Id INT PRIMARY KEY,
Name VARCHAR2(10)
);
Insert into parent_table values (10, 'IT');
Insert into parent_table values (20, 'HR');
Insert into parent_table values (30, 'INFRA');
--employees
CREATE TABLE source_table(
Id INT PRIMARY KEY,
Name VARCHAR2(20) NOT NULL,
DepartmentID INT REFERENCES parent_table(Id)
);
INSERT into source_table VALUES (101, 'Anurag111111111', 10);
INSERT into source_table VALUES (102, 'Pranaya11111111', 20);
INSERT into source_table VALUES (103, 'Hina11111111111', 30);
--a copy of source
CREATE TABLE target_table(
Id INT PRIMARY KEY,
Name VARCHAR2(10) NOT NULL,
DepartmentID INT REFERENCES parent_table(Id)
);
INSERT into target_table VALUES (101, 'Anurag', 10);
Snowflake
CREATE OR REPLACE TABLE error_table (
ERROR VARCHAR(16777216),
FILE VARCHAR(16777216),
LINE NUMBER(18,0),
CHARACTER NUMBER(18,0),
CATEGORY VARCHAR(16777216),
CODE NUMBER(18,0),
SQL_STATE VARCHAR(16777216),
COLUMN_NAME VARCHAR(16777216),
ROW_NUMBER NUMBER(18,0),
REJECTED_RECORD VARCHAR(16777216),
CREATE_TS TIMESTAMP DEFAULT current_timestamp
);
--departments
CREATE OR REPLACE TABLE parent_table(
Id INT PRIMARY KEY,
Name VARCHAR2(10)
);
INSERT INTO parent_table VALUES (10, 'IT');
INSERT INTO parent_table VALUES (20, 'HR');
INSERT INTO parent_table VALUES (30, 'INFRA');
--employees
CREATE OR REPLACE TABLE source_table(
Id INT PRIMARY KEY,
Name VARCHAR2(20) NOT NULL,
DepartmentID INT REFERENCES parent_table(Id)
);
INSERT INTO source_table VALUES (101, 'Anurag111111111', 10);
INSERT INTO source_table VALUES (102, 'Pranaya11111111', 20);
INSERT INTO source_table VALUES (103, 'Hina11111111111', 30);
--a copy of employees
CREATE OR REPLACE TABLE target_table(
Id INT PRIMARY KEY,
Name VARCHAR2(10) NOT NULL,
DepartmentID INT REFERENCES parent_table(Id)
);
INSERT into target_table VALUES (101, 'Anurag', 200000);
1. MERGE INTO Inside a FORALL
Oracle
The three cases below have the same transformation to Snowflake Scripting and are functionally equivalent.
CREATE OR REPLACE PROCEDURE procedure_example (
department_id_in IN source_table.DepartmentID%TYPE)
IS
TYPE employee_ids_t IS TABLE OF source_table%ROWTYPE
INDEX BY PLS_INTEGER;
employee_list employee_ids_t;
BEGIN
SELECT *
BULK COLLECT INTO employee_list
FROM source_table
WHERE DepartmentID = procedure_example.department_id_in;
FORALL indx IN 1 .. employee_list.COUNT
MERGE INTO target_table
USING (SELECT * FROM DUAL) src
ON (id = employee_list(indx).id)
WHEN MATCHED THEN
UPDATE SET
name = employee_list(indx).Name
WHEN NOT MATCHED THEN
INSERT (Id, Name, DepartmentID)
VALUES (employee_list(indx).Id, employee_list(indx).Name, employee_list(indx).DepartmentID)
LOG ERRORS INTO error_table('MERGE INTO ERROR')
REJECT LIMIT UNLIMITED;
END;
CALL procedure_example(10);
select * from target_table;
select * from error_table;
Snowflake
--Generated by SnowConvert---------------
CREATE OR REPLACE TRANSIENT TABLE target_staging_table(
Id INT PRIMARY KEY,
Name VARCHAR2(10) NOT NULL,
DepartmentID INT REFERENCES parent_table(Id)
);
--Generated by SnowConvert---------------
CREATE OR REPLACE PROCEDURE procedure_example (DEPARTMENT_ID_IN INT /*** MSC-WARNING - MSCEWI3129 - TYPE ATTRIBUTE 'employees.DepartmentID%TYPE' COULD NOT BE RESOLVED, SO IT WAS TRANSFORMED TO VARIANT ***/)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
CREATE OR REPLACE TEMP TABLE SOURCE_TEMPORAL AS
WITH source_data as (
SELECT *
FROM source_table
WHERE DEPARTMENTID =: DEPARTMENT_ID_IN
)
SELECT source_data.*, parent_table.id as PARENT_KEY
FROM source_data
left join parent_table on source_data.DepartmentID = parent_table.id;
--All records violating foreign key integrity
INSERT INTO error_table (ERROR, COLUMN_NAME, REJECTED_RECORD)
SELECT
'Foreign Key Constraint Violated' ERROR,'KEY_COL' COLUMN_NAME, id
FROM SOURCE_TEMPORAL
WHERE PARENT_KEY IS NULL;
DELETE FROM SOURCE_TEMPORAL
WHERE PARENT_KEY IS NULL;
BEGIN
MERGE INTO target_table
USING SOURCE_TEMPORAL SRC
ON SRC.id = target_table.id
WHEN MATCHED THEN
UPDATE SET
name = SRC.name
WHEN NOT MATCHED THEN
INSERT (Id, Name, DepartmentID)
VALUES (SRC.Id, SRC.Name, SRC.DepartmentID);
EXCEPTION
WHEN OTHER THEN
CREATE OR REPLACE TEMPORARY STAGE my_int_stage
COPY_OPTIONS = (ON_ERROR='continue');
--Create my file and populate with data
COPY INTO @my_int_stage/my_file FROM (
SELECT * exclude(PARENT_KEY) FROM SOURCE_TEMPORAL
) OVERWRITE = TRUE ;
COPY INTO target_staging_table(id, name, DepartmentID)
FROM (
SELECT
-- distinct
t.$1, t.$2, t.$3
FROM @my_int_stage/my_file t
) ON_ERROR = CONTINUE;
INSERT INTO ERROR_TABLE (ERROR, FILE, LINE, CHARACTER, CATEGORY, CODE, SQL_STATE, COLUMN_NAME, ROW_NUMBER, REJECTED_RECORD)
SELECT
ERROR, FILE,LINE, CHARACTER, CATEGORY, CODE, SQL_STATE, COLUMN_NAME, ROW_NUMBER, REJECTED_RECORD
FROM TABLE(VALIDATE(target_staging_table, JOB_ID => '_last')) order by line; --The last charge on the current session
MERGE INTO target_table
USING target_staging_table staging
ON staging.id = target_table.id
WHEN MATCHED THEN
UPDATE SET
name = staging.name
WHEN NOT MATCHED THEN
INSERT (Id, Name, DepartmentID)
VALUES (staging.Id, staging.Name, staging.DepartmentID);
END;
return 'Awesome!';
END;
$$;
CALL procedure_example(10);
select * from target_table;
select * from error_table;
2. FORALL With INSERT INTO
Oracle
CREATE OR REPLACE PROCEDURE myProcedure IS
CURSOR cursorVariable IS
SELECT * FROM table1;
TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
collectionVariable collectionTypeDefinition;
BEGIN
OPEN cursorVariable;
LOOP
FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 2;
EXIT WHEN collectionVariable.COUNT = 0;
FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
INSERT INTO table2 VALUES collectionVariable(forIndex);
collectionVariable.DELETE;
END LOOP;
CLOSE cursorVariable;
END;
Snowflake
CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
--** MSC-WARNING - MSCCP0005 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
--** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
INSERT INTO table2
(
SELECT
* FROM
table1
);
END;
$$;
3. FORALL With Multiple Fetched Collections
Oracle
CREATE OR REPLACE PROCEDURE myProcedure IS
CURSOR cursorVariable IS
SELECT * FROM table1;
column1Collection dbms_sql.NUMBER_table;
column2Collection dbms_sql.NUMBER_table;
BEGIN
OPEN cursorVariable;
LOOP
FETCH cursorVariable BULK COLLECT INTO column1Collection, column2Collection limit 20;
EXIT WHEN column1Collection.COUNT = 0;
FORALL forIndex IN 1..column1Collection.COUNT
INSERT INTO table2 VALUES (
column1Collection(forIndex),
column2Collection(forIndex)
);
END LOOP;
CLOSE cursorVariable;
END;
Snowflake
CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
--** MSC-WARNING - MSCCP0005 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
--** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
INSERT INTO table2
(
SELECT
$1,
$2
FROM
table1
);
END;
$$;
4. FORALL With Record of Collections
Oracle
CREATE OR REPLACE PROCEDURE myProcedure IS
CURSOR cursorVariable IS
SELECT * FROM table1;
TYPE recordType IS RECORD(
column1Collection dbms_sql.NUMBER_table,
column2Collection dbms_sql.NUMBER_table
);
columnRecord recordType;
BEGIN
OPEN cursorVariable;
LOOP
FETCH cursorVariable BULK COLLECT INTO columnRecord.column1Collection, columnRecord.column2Collection limit 20;
FORALL forIndex IN 1..columnRecord.column1Collection.COUNT
INSERT INTO table2 VALUES (
columnRecord.column1Collection(forIndex),
columnRecord.column2Collection(forIndex)
);
EXIT WHEN cursorVariable%NOTFOUND;
END LOOP;
CLOSE cursorVariable;
END;
Snowflake
CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
--** MSC-WARNING - MSCCP0005 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
--** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
INSERT INTO table2
(
SELECT
$1,
$2
FROM
table1
);
END;
$$;
5. FORALL With Dynamic SQL
Oracle
CREATE OR REPLACE PROCEDURE myProcedure IS
cursorVariable SYS_REFCURSOR;
TYPE collectionTypeDefinition IS
TABLE OF table1%ROWTYPE;
collectionVariable collectionTypeDefinition;
query VARCHAR(200) := 'SELECT * FROM table1';
BEGIN
OPEN cursorVariable FOR query;
LOOP
FETCH cursorVariable BULK COLLECT INTO collectionVariable;
EXIT WHEN collectionVariable.COUNT = 0;
FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
INSERT INTO table2 VALUES collectionVariable(forIndex);
collectionVariable.DELETE;
END LOOP;
CLOSE cursorVariable;
END;
Snowflake
CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
query VARCHAR(200) := 'SELECT * FROM table1';
BEGIN
--** MSC-WARNING - MSCCP0005 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
--** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
--** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
EXECUTE IMMEDIATE 'CREATE OR REPLACE TEMPORARY TABLE query AS ' || :query;
INSERT INTO table2
(
SELECT
*
FROM
query
);
END;
$$;
Oracle
Snowflake
CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
intVariable INTEGER := 7;
BEGIN
--** MSC-WARNING - MSCCP0005 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
--** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
INSERT INTO table2
(
SELECT * FROM table1
WHERE
column2 = :intVariable OR
column1 BETWEEN param1 AND param2
);
END;
$$;
8. FORALL Without LOOPS
Oracle
CREATE OR REPLACE PROCEDURE myProcedure IS
TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
collectionVariable collectionTypeDefinition;
BEGIN
SELECT * BULK COLLECT INTO collectionVariable FROM table1;
FORALL forIndex IN 1..collectionVariable.COUNT
INSERT INTO table2 VALUES (
collectionVariable (forIndex).column1,
collectionVariable (forIndex).column2
);
collectionVariable.DELETE;
END;
Snowflake
CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
--** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
INSERT INTO table2
(
SELECT
column1,
column2
FROM
table1
);
END;
$$;
9. FORALL With UPDATE Statements
Oracle
CREATE OR REPLACE PROCEDURE myProcedure IS
CURSOR cursorVariable IS
SELECT * FROM table1;
TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
collectionVariable collectionTypeDefinition;
BEGIN
OPEN cursorVariable;
LOOP
FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 2;
EXIT WHEN collectionVariable.COUNT = 0;
FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
UPDATE table2 SET column1 = '54321' WHERE column2 = collectionVariable(forIndex).column2;
collectionVariable.DELETE;
END LOOP;
CLOSE cursorVariable;
END;
Snowflake
CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
--** MSC-WARNING - MSCCP0005 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
--** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
UPDATE table2
SET column1 = '54321'
FROM (
SELECT * FROM table1
) AS collectionVariable
WHERE column2 = collectionVariable.column2;
END;
$$;
10. FORALL With DELETE Statements
Oracle
CREATE OR REPLACE PROCEDURE myProcedure IS
CURSOR cursorVariable IS
SELECT * FROM table1;
TYPE collectionTypeDefinition IS TABLE OF table1%ROWTYPE;
collectionVariable collectionTypeDefinition;
BEGIN
OPEN cursorVariable;
LOOP
FETCH cursorVariable BULK COLLECT INTO collectionVariable limit 2;
EXIT WHEN collectionVariable.COUNT = 0;
FORALL forIndex IN collectionVariable.FIRST..collectionVariable.LAST
DELETE FROM table2 WHERE column2 = collectionVariable(forIndex).column2;
collectionVariable.DELETE;
END LOOP;
CLOSE cursorVariable;
END;
Snowflake
CREATE OR REPLACE PROCEDURE myProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
--** MSC-WARNING - MSCCP0005 - THIS STATEMENT HAS USAGES OF CURSOR FETCH BULK OPERATIONS **
--** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
DELETE FROM table2
USING (
SELECT * FROM table1
) collectionVariable
WHERE
table2.column2 = collectionVariable.column2;
END;
$$;
Related EWIs
Last updated
Was this helpful?