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;
ID| NAME| DEPARTMENTID|
-----+--------+-------------+
101| Anurag| 10|
ORA_ERR_NUMBER$| ORA_ERR_MESG$ | ORA_ERR_ROWID$| ORA_ERR_OPTYP$ | ORA_ERR_TAG$ |
---------------+--------------------------------------------------------------------------------------+-------------------+----------------+--------------------+
12899| ORA-12899: value too large for column "TARGET_TABLE"."NAME" (actual: 15, maximum: 10)| AK6vdpADDAAABI7AAA| U |. MERGE INTO ERROR |
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;
COLUMN1| COLUMN2|
--------------------+-----------------------+
1.000000000000000000| 2.000000000000000000|
1.000000000000000000| 2.000000000000000000|
2.000000000000000000| 3.000000000000000000|
3.000000000000000000| 4.000000000000000000|
4.000000000000000000| 5.000000000000000000|
5.000000000000000000| 6.000000000000000000|
The EWIs MSCCP0005 and MSCCP0006 are added in every FETCH BULK COLLECT occurrence into FORALL statement.
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;
COLUMN1| COLUMN2|
--------+--------+
1| 2|
1| 2|
2| 3|
3| 4|
4| 5|
5| 6|
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;
$$;
COLUMN1| COLUMN2|
--------------------+-----------------------+
1.000000000000000000| 2.000000000000000000|
1.000000000000000000| 2.000000000000000000|
2.000000000000000000| 3.000000000000000000|
3.000000000000000000| 4.000000000000000000|
4.000000000000000000| 5.000000000000000000|
5.000000000000000000| 6.000000000000000000|
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;
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 2;
EXIT WHEN column1Collection.COUNT = 0;
FORALL forIndex IN 1..column1Collection.COUNT
UPDATE table2 SET column2 = column2Collection(forIndex)
WHERE column1 = column1Collection(forIndex);
END LOOP;
CLOSE cursorVariable;
END;
COLUMN1| COLUMN2|
--------+--------+
1| 2|
2| 3|
3| 4|
4| 5|
5| 6|
1| 2|
COLUMN1| COLUMN2|
--------+--------+
1| 2|
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;
$$;
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 column2 = column1Collection.$2
FROM (
SELECT * FROM table1
) AS column1Collection
WHERE column1 = column1Collection.$1;
END;
$$;
COLUMN1| COLUMN2|
--------------------+-----------------------+
1.000000000000000000| 2.000000000000000000|
1.000000000000000000| 2.000000000000000000|
2.000000000000000000| 3.000000000000000000|
3.000000000000000000| 4.000000000000000000|
4.000000000000000000| 5.000000000000000000|
5.000000000000000000| 6.000000000000000000|
COLUMN1| COLUMN2|
--------------------+-----------------------+
1.000000000000000000| 2.000000000000000000|
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;
COLUMN1| COLUMN2|
--------+--------+
1| 2|
1| 2|
2| 3|
3| 4|
4| 5|
5| 6|
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;
$$;
COLUMN1| COLUMN2|
--------------------+-----------------------+
1.000000000000000000| 2.000000000000000000|
1.000000000000000000| 2.000000000000000000|
2.000000000000000000| 3.000000000000000000|
3.000000000000000000| 4.000000000000000000|
4.000000000000000000| 5.000000000000000000|
5.000000000000000000| 6.000000000000000000|
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;
COLUMN1| COLUMN2|
--------+--------+
1| 2|
1| 2|
2| 3|
3| 4|
4| 5|
5| 6|
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;
$$;
COLUMN1| COLUMN2|
--------------------+-----------------------+
1.000000000000000000| 2.000000000000000000|
1.000000000000000000| 2.000000000000000000|
2.000000000000000000| 3.000000000000000000|
3.000000000000000000| 4.000000000000000000|
4.000000000000000000| 5.000000000000000000|
5.000000000000000000| 6.000
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;
$$;
Comming soon
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;
COLUMN1| COLUMN2|
--------+--------+
1| 2|
1| 2|
2| 3|
3| 4|
4| 5|
5| 6|
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;
$$;
COLUMN1| COLUMN2|
--------------------+-----------------------+
1.000000000000000000| 2.000000000000000000|
1.000000000000000000| 2.000000000000000000|
2.000000000000000000| 3.000000000000000000|
3.000000000000000000| 4.000000000000000000|
4.000000000000000000| 5.000000000000000000|
5.000000000000000000| 6.000000000000000000|
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;
COLUMN1| COLUMN2|
--------+--------+
54321| 2|
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;
$$;
ambiguous column name 'COLUMN2'
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;
no data found
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;
$$;
Query produced no results
Related EWIs
Last updated