LOG ERROR

Description

The FORALL statement runs one DML statement multiple times, with different values in the VALUES and WHERE 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;

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;

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|

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;
$$;

             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;

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;
$$;

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;
$$;

             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;

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;
$$;

MSCCP0004: This statement has usages of dynamic SQL.
MSCCP0005: This statement has usages of cursor fetch bulk operations.
MSCCP0006: Fetch inside a loop is considered a complex pattern, this could degrade Snowflake performance.

PreviousIS EMPTY NextLOOP

Last updated 2 years ago

Was this helpful?

Description

Sample Source Patterns

Setup Data

Oracle

Snowflake

1. MERGE INTO Inside a FORALL

Oracle

Snowflake

2. FORALL With INSERT INTO

Oracle

Snowflake

3. FORALL With Multiple Fetched Collections

Oracle

Snowflake

4. FORALL With Record of Collections

Oracle

Snowflake

5. FORALL With Dynamic SQL

Oracle

Snowflake

Oracle

Snowflake

8. FORALL Without LOOPS

Oracle

Snowflake

9. FORALL With UPDATE Statements

Oracle

Snowflake

10. FORALL With DELETE Statements

Oracle

Snowflake

Related EWIs