SnowConvert AI - Oracle - DML STATEMENTS¶
Description ¶
DML statement extensions differ from normal DML statements because they can use PL/SQL elements like collections and records. So far some of these elements are not supported by snowflake scripting. If one statement is not supported, an EWI will be added during the translation. Other DML statements will be translated as if they were not inside a procedure.
INSERT Statement Extension¶
Translation reference to convert Oracle INSERT Statement Extension to Snowflake Scripting
Note
Some parts in the output code are omitted for clarity reasons.
Description¶
The PL/SQL extension to the SQL
INSERT
statement lets you specify a record name in thevalues_clause
of thesingle_table_insert
instead of specifying a column list in theinsert_into_clause.
(Oracle PL/SQL Language Reference INSERT Statement Extension)
Snowflake INSERT INTO differs from Snowflake Scripting in variable constraints; needing to have the names preceded by a colon ‘:’ in order to bind the variables’ value.
Recommendations¶
Note
This code was executed to a better understanding of the examples:
Oracle¶
CREATE TABLE numbers_table(num integer, word varchar2(20));
Snowflake¶
CREATE OR REPLACE TABLE PUBLIC.numbers_table (num integer,
word VARCHAR(20));
INSERT Statement Extension simple case¶
Oracle¶
CREATE OR REPLACE PROCEDURE proc_insert_statement
AS
number_variable integer := 10;
word_variable varchar2(20) := 'ten';
BEGIN
INSERT INTO numbers_table VALUES(number_variable, word_variable);
INSERT INTO numbers_table VALUES(11, 'eleven');
END;
CALL proc_insert_statement();
SELECT * FROM numbers_table ;
Result¶
NUM |
WORD |
---|---|
10 |
ten |
11 |
eleven |
Snowflake Scripting¶
CREATE OR REPLACE PROCEDURE proc_insert_statement ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
number_variable integer := 10;
word_variable VARCHAR(20) := 'ten';
BEGIN
INSERT INTO numbers_table
VALUES(:number_variable, :word_variable);
INSERT INTO numbers_table
VALUES(11, 'eleven');
END;
$$;
CALL proc_insert_statement();
SELECT * FROM
numbers_table;
Result¶
NUM |
WORD |
---|---|
10 |
ten |
11 |
eleven |
Known Issues¶
1. Records are not supported by Snowflake Scripting¶
Since records are not supported by snowflake scripting, instead of using the VALUES record
clause, it is necessary to change it into a SELECT clause and split the columns of the record. For more information please see the Record Type Definition Section.
MERGE Statement¶
Translation reference to convert Oracle MERGE statement to Snowflake Scripting
Note
Some parts in the output code are omitted for clarity reasons.
Description¶
The
MERGE
statement is used to select rows from one or more sources for update or insertion into a table or view. It is possible to specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations. It lets to avoid multipleINSERT
,UPDATE
, andDELETE
DML statements.MERGE
is a deterministic statement. It is not possible to update the same row of the target table multiple times in the sameMERGE
statement. (Oracle PL/SQL Language Reference MERGE Statement)
Oracle MERGE Syntax¶
MERGE [ hint ]
INTO [ schema. ] { table | view } [ t_alias ]
USING { [ schema. ] { table | view }
| ( subquery )
} [ t_alias ]
ON ( condition )
[ merge_update_clause ]
[ merge_insert_clause ]
[ error_logging_clause ] ;
merge_update_clause := WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
[, column = { expr | DEFAULT } ]...
[ where_clause ]
[ DELETE where_clause ]
merge_insert_clause := WHEN NOT MATCHED THEN
INSERT [ (column [, column ]...) ]
VALUES ({ expr | DEFAULT }
[, { expr | DEFAULT } ]...
)
[ where_clause ]
error_logging_clause := LOG ERRORS
[ INTO [schema.] table ]
[ (simple_expression) ]
[ REJECT LIMIT { integer | UNLIMITED } ]
where_clause := WHERE condition
Snowflake Scripting MERGE Syntax¶
MERGE INTO <target_table> USING <source> ON <join_expr>
{ matchedClause | notMatchedClause } [ ... ]
matchedClause ::= WHEN MATCHED [ AND <case_predicate> ]
THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> ... ] | DELETE } [ ... ]
notMatchedClause ::= WHEN NOT MATCHED [ AND <case_predicate> ]
THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )
Sample Source Patterns¶
Sample auxiliary data¶
Note
This code was executed for a better understanding of the examples:
Oracle¶
CREATE TABLE people_source (
person_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name VARCHAR2(20) NOT NULL,
title VARCHAR2(10) NOT NULL
);
CREATE TABLE people_target (
person_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR2(20) NOT NULL,
last_name VARCHAR2(20) NOT NULL,
title VARCHAR2(10) NOT NULL
);
CREATE TABLE bonuses (
employee_id NUMBER,
bonus NUMBER DEFAULT 100
);
INSERT INTO people_target
VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO people_target
VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO people_source
VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO people_source
VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO people_source
VALUES (4, 'Dave', 'Brown', 'Mr');
INSERT INTO
bonuses(employee_id) (
SELECT
e.employee_id
FROM
hr.employees e,
oe.orders o
WHERE
e.employee_id = o.sales_rep_id
GROUP BY
e.employee_id
);
Snowflake¶
CREATE OR REPLACE TABLE people_source (
person_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
title VARCHAR(10) NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
CREATE OR REPLACE TABLE people_target (
person_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
title VARCHAR(10) NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
CREATE OR REPLACE TABLE bonuses (
employee_id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
bonus NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ DEFAULT 100
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO people_target
VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO people_target
VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO people_source
VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO people_source
VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO people_source
VALUES (4, 'Dave', 'Brown', 'Mr');
INSERT INTO bonuses(employee_id) (
SELECT
e.employee_id
FROM
hr.employees e,
oe.orders o
WHERE
e.employee_id = o.sales_rep_id
GROUP BY
e.employee_id
);
MERGE Statement simple case¶
Oracle¶
MERGE INTO people_target pt USING people_source ps ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN
UPDATE
SET
pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title
WHEN NOT MATCHED THEN
INSERT
(
pt.person_id,
pt.first_name,
pt.last_name,
pt.title
)
VALUES
(
ps.person_id,
ps.first_name,
ps.last_name,
ps.title
);
SELECT * FROM people_target;
Result¶
PERSON_ID |
FIRST_NAME |
LAST_NAME |
TITLE |
---|---|---|---|
1 |
John |
Smith |
Mr |
2 |
Alice |
Jones |
Mrs. |
3 |
Jane |
Doe |
Miss |
4 |
Dave |
Brown |
Mr |
Snowflake¶
MERGE INTO people_target pt USING people_source ps ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN
UPDATE
SET
pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title
WHEN NOT MATCHED THEN
INSERT
(
pt.person_id,
pt.first_name,
pt.last_name,
pt.title
)
VALUES
(
ps.person_id,
ps.first_name,
ps.last_name,
ps.title
);
SELECT * FROM
people_target;
Result¶
PERSON_ID |
FIRST_NAME |
LAST_NAME |
TITLE |
---|---|---|---|
1 |
John |
Smith |
Mr |
2 |
Alice |
Jones |
Mrs. |
3 |
Jane |
Doe |
Miss |
4 |
Dave |
Brown |
Mr |
MERGE Statement with DELETE and where clause¶
In order to find an equivalence for the DELETE statement and the where clause, it is necessary to reorder and implement some changes in the Snowflake merge statement.
Changed required:¶
Replace the Oracle’s DELETE where_clause with a new Snowflake’s matchedClause with the AND predicate statement
Replace the where_clause from the Oracle’s merge_insert_clause with an AND predicate statement in the Snowflake’s notMatchedClause
Oracle¶
MERGE INTO bonuses D USING (
SELECT
employee_id,
salary,
department_id
FROM
hr.employees
WHERE
department_id = 80
) S ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN
UPDATE
SET
D.bonus = D.bonus + S.salary *.01 DELETE
WHERE
(S.salary > 8000)
WHEN NOT MATCHED THEN
INSERT
(D.employee_id, D.bonus)
VALUES
(S.employee_id, S.salary *.01)
WHERE
(S.salary <= 8000);
SELECT * FROM bonuses ORDER BY employee_id;
Result¶
EMPLOYEE_ID |
BONUS |
---|---|
153 |
180 |
154 |
175 |
155 |
170 |
159 |
180 |
160 |
175 |
161 |
170 |
164 |
72 |
165 |
68 |
166 |
64 |
167 |
62 |
171 |
74 |
172 |
73 |
173 |
61 |
179 |
62 |
Snowflake¶
--** SSC-FDM-OR0018 - SNOWFLAKE MERGE STATEMENT MAY HAVE SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE **
MERGE INTO bonuses D USING (
SELECT
employee_id,
salary,
department_id
FROM
hr.employees
WHERE
department_id = 80) S ON (D.employee_id = S.employee_id)
WHEN MATCHED AND
(S.salary > 8000) THEN
DELETE
WHEN MATCHED THEN
UPDATE SET
D.bonus = D.bonus + S.salary *.01
WHEN NOT MATCHED AND
(S.salary <= 8000) THEN
INSERT
(D.employee_id, D.bonus)
VALUES
(S.employee_id, S.salary *.01);
SELECT * FROM
bonuses
ORDER BY employee_id;
Result¶
EMPLOYEE_ID |
BONUS |
---|---|
153 |
180 |
154 |
175 |
155 |
170 |
159 |
180 |
160 |
175 |
161 |
170 |
164 |
72 |
165 |
68 |
166 |
64 |
167 |
62 |
171 |
74 |
172 |
73 |
173 |
61 |
179 |
62 |
Warning
In some cases the changes applied may do not work as expected, like the next example:
Oracle¶
MERGE INTO people_target pt USING people_source ps ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN
UPDATE
SET
pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title DELETE
where
pt.title = 'Mrs.'
WHEN NOT MATCHED THEN
INSERT
(
pt.person_id,
pt.first_name,
pt.last_name,
pt.title
)
VALUES
(
ps.person_id,
ps.first_name,
ps.last_name,
ps.title
)
WHERE
ps.title = 'Mr';
SELECT * FROM people_target;
Result¶
PERSON_ID |
FIRST_NAME |
LAST_NAME |
TITLE |
---|---|---|---|
1 |
John |
Smith |
Mr |
4 |
Dave |
Brown |
Mr |
Snowflake¶
--** SSC-FDM-OR0018 - SNOWFLAKE MERGE STATEMENT MAY HAVE SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE **
MERGE INTO people_target pt USING people_source ps ON (pt.person_id = ps.person_id)
WHEN MATCHED AND
pt.title = 'Mrs.' THEN
DELETE
WHEN MATCHED THEN
UPDATE SET
pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = ps.title
WHEN NOT MATCHED AND
ps.title = 'Mr' THEN
INSERT
(
pt.person_id,
pt.first_name,
pt.last_name,
pt.title
)
VALUES
(
ps.person_id,
ps.first_name,
ps.last_name,
ps.title
);
SELECT * FROM
people_target;
Result¶
PERSON_ID |
FIRST_NAME |
LAST_NAME |
TITLE |
---|---|---|---|
1 |
John |
Smith |
Mr |
2 |
Alice |
Jones |
Mrs. |
4 |
Dave |
Brown |
Mr |
Known Issues¶
1. Oracle’s error_logging_clause is not supported¶
There is no equivalent for the error logging clause in Snowflake Scripting.
2. Changed applied do not work as expected¶
Sometimes, the changes applied in order to achieve the functional equivalence between Oracle’s merge statement and Snowflake’s do not work as expected.
Related EWIs¶
SSC-FDM-0006: Number type column may not behave similarly in Snowflake.
SSC-FDM-OR0018: Merge statement may not work as expected
SELECT INTO Statement¶
Translation reference to convert Oracle SELECT INTO statement to Snowflake Scripting
Note
Some parts in the output code are omitted for clarity reasons.
Description¶
The
SELECT
INTO
statement retrieves values from one or more database tables (as the SQLSELECT
statement does) and stores them in variables (which the SQLSELECT
statement does not do). (Oracle PL/SQL Language Reference SELECT INTO Statement)
Oracle SELECT INTO Syntax¶
SELECT [ { DISTINCT | UNIQUE } | ALL ] select_list
{ into_clause | bulk_collect_into_clause } FROM rest-of-statement ;
Oracle Into Clause Syntax¶
INTO { variable [, variable ]... | record )
Oracle Bulk Collect Syntax¶
BULK COLLECT INTO { collection | :host_array }
[, { collection | :host_array } ]...
Snowflake Scripting SELECT INTO Syntax¶
SELECT [ { ALL | DISTINCT } ]
{
[{<object_name>|<alias>}.]*
| [{<object_name>|<alias>}.]<col_name>
| [{<object_name>|<alias>}.]$<col_position>
| <expr>
[ [ AS ] <col_alias> ]
}
[ , ... ]
INTO :<variable> [, :<variable> ... ]
[...]
Sample Source Patterns¶
Sample auxiliary data¶
Note
This code was executed to a better understanding of the examples:
Oracle¶
CREATE TABLE numbers_table(num integer, word varchar2(20));
INSERT INTO numbers_table VALUES (1, 'one');
CREATE TABLE aux_numbers_table(aux_num integer, aux_word varchar2(20));
Snowflake¶
CREATE OR REPLACE TABLE numbers_table (num integer,
word VARCHAR(20))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO numbers_table
VALUES (1, 'one');
CREATE OR REPLACE TABLE aux_numbers_table (aux_num integer,
aux_word VARCHAR(20))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
SELECT INTO Statement simple case¶
Oracle¶
CREATE OR REPLACE PROCEDURE proc_select_into_variables
AS
number_variable integer;
word_variable varchar2(20);
BEGIN
SELECT * INTO number_variable, word_variable FROM numbers_table;
INSERT INTO aux_numbers_table VALUES(number_variable, word_variable);
END;
CALL proc_select_into_variables();
SELECT * FROM aux_numbers_table;
Result¶
AUX_NUM |
AUX_WORD |
---|---|
1 |
one |
Snowflake Scripting¶
CREATE OR REPLACE PROCEDURE proc_select_into_variables ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
number_variable integer;
word_variable VARCHAR(20);
BEGIN
SELECT * INTO
:number_variable,
:word_variable
FROM
numbers_table;
INSERT INTO aux_numbers_table
VALUES(:number_variable, :word_variable);
END;
$$;
CALL proc_select_into_variables();
SELECT * FROM
aux_numbers_table;
Result¶
|AUX_NUM|AUX_WORD|
|-------|--------|
|1 |one |
Known Issues¶
1. BULK COLLECT INTO is not supported¶
Snowflake Scripting does not support the BULK COLLECT INTO clause. However, it is possible to use ARRAY_AGG to construct a new variable. For more information please see the Collection Bulk Operations Section.
2. Collections and records are not supported¶
Snowflake Scripting does not support the use of collections nor records. It is possible to migrate them using Semi-structured data types as explained in this section.
Related EWIs¶
No related EWIs.
Work around to simulate the use of Records¶
Warning
This page is deprecated but was left for compatibility purposes. If you want to see the updated section, please refer to Collections And Records
Description¶
This section describes how to simulate the behavior of Oracle records in SELECT and INSERT Statements, using RESULTSET and CURSORS of Snowflake Scripting.
Snowflake Scripting RESULTSET and CURSOR¶
Snowflake RESULTSET Syntax¶
<resultset_name> RESULTSET [ DEFAULT ( <query> ) ] ;
LET <resultset_name> RESULTSET [ { DEFAULT | := } ( <query> ) ] ;
LET <resultset_name> RESULTSET [ { DEFAULT | := } ( <query> ) ] ;
Recommendations¶
Note
For the following examples, this code was executed to better understanding of the examples:
Oracle¶
CREATE TABLE numbers_table(num integer, word varchar2(20));
INSERT INTO numbers_table VALUES (1, 'one');
CREATE TABLE aux_numbers_table(aux_num integer, aux_word varchar2(20));
Snowflake¶
CREATE OR REPLACE TABLE numbers_table (num integer,
word VARCHAR(20))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO numbers_table
VALUES (1, 'one');
CREATE OR REPLACE TABLE aux_numbers_table (aux_num integer,
aux_word VARCHAR(20))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
Using RESULTSET and Cursors instead of Records¶
Oracle¶
CREATE OR REPLACE PROCEDURE proc_insert_select_resultset
AS
TYPE number_record_definition IS RECORD(
rec_num numbers_table.num%type,
rec_word numbers_table.word%type
);
number_record number_record_definition;
BEGIN
SELECT * INTO number_record FROM numbers_table;
INSERT INTO aux_numbers_table VALUES number_record;
END;
CALL proc_insert_select_resultset();
SELECT * FROM aux_numbers_table;
Result¶
AUX_NUM |
AUX_WORD |
---|---|
1 |
one |
Snowflake¶
CREATE OR REPLACE PROCEDURE proc_insert_select_resultset ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
TYPE number_record_definition IS RECORD(
rec_num numbers_table.num%type,
rec_word numbers_table.word%type
);
number_record OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - number_record_definition DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
BEGIN
SELECT
OBJECT_CONSTRUCT( *) INTO
:number_record
FROM
numbers_table;
INSERT INTO aux_numbers_table
SELECT
:number_record:REC_NUM,
:number_record:REC_WORD;
END;
$$;
CALL proc_insert_select_resultset();
SELECT * FROM
aux_numbers_table;
using cursor
CREATE OR REPLACE PROCEDURE PUBLIC.proc_select_into()
RETURNS INTEGER
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
$$
DECLARE
NUMBER_VARIABLE INTEGER;
WORD_VARIABLE VARCHAR;
NUMBER_RECORD RESULTSET;
BEGIN
LET c2 CURSOR FOR NUMBER_RECORD;
FOR row_variable IN c2 DO
let var1 integer := row_variable.num;
let var2 varchar := row_variable.word;
INSERT INTO PUBLIC.aux_numbers_table VALUES(:var1, :var2);
END FOR;
end;
$$;
Result¶
AUX_NUM |
AUX_WORD |
---|---|
1 |
one |
Known Issues¶
1. Limitation in the use of RESULTSET¶
RESULTSET is very limited in its use. If table(result_scan(last_query_id()))
statement, should be used just after the RESULTSET’s query is executed. For further information check this link.
Related EWIs¶
SSC-EWI-0036: Data type converted to another data type.
SSC-EWI-0056: Create Type Not Supported.