FORALL
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
IN -> Oracle_01.sql
CREATE TABLE table1 (
column1 NUMBER,
column2 NUMBER
);
INSERT INTO table1 (column1, column2) VALUES (1, 2);
INSERT INTO table1 (column1, column2) VALUES (2, 3);
INSERT INTO table1 (column1, column2) VALUES (3, 4);
INSERT INTO table1 (column1, column2) VALUES (4, 5);
INSERT INTO table1 (column1, column2) VALUES (5, 6);
CREATE TABLE table2 (
column1 NUMBER,
column2 NUMBER
);
INSERT INTO table2 (column1, column2) VALUES (1, 2);
IN -> Oracle_02.sql
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
OUT -> Oracle_01.sql
CREATE OR REPLACE TABLE table1 (
column1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
column2 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO table1(column1, column2) VALUES (1, 2);
INSERT INTO table1(column1, column2) VALUES (2, 3);
INSERT INTO table1(column1, column2) VALUES (3, 4);
INSERT INTO table1(column1, column2) VALUES (4, 5);
INSERT INTO table1(column1, column2) VALUES (5, 6);
CREATE OR REPLACE TABLE table2 (
column1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
column2 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO table2(column1, column2) VALUES (1, 2);
OUT -> Oracle_02.sql
CREATE OR REPLACE TABLE error_table (
"ORA_ERR_NUMBER$" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
"ORA_ERR_MESG$" VARCHAR(2000),
"ORA_ERR_ROWID$" VARCHAR(18) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWID DATA TYPE CONVERTED TO VARCHAR ***/!!!,
"ORA_ERR_OPTYP$" VARCHAR(2),
"ORA_ERR_TAG$" VARCHAR(2000)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
--departments
CREATE OR REPLACE TABLE parent_table (
Id INT PRIMARY KEY,
Name VARCHAR(10)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO parent_table
VALUES (10, 'IT');
INSERT INTO parent_table