Expressions and operators
Expressions
Concatenation Operator
Oracle concatenation is achieved in JavaScript using Template literal. Also it uses the Concat Helper to properly handle concatenations with nulls.
Oracle
CREATE OR REPLACE PROCEDURE CONCAT_TEST
IS
NUM1 INTEGER := 123;
NUM2 INTEGER := 321;
VAR1 VARCHAR(10) := 'value';
concat_var VARCHAR(100);
sql_stmt VARCHAR(100);
BEGIN
concat_var := NUM1 || NUM2 || VAR1 || 'literal';
sql_stmt := 'INSERT INTO t1 VALUES (''' || concat_var || ''')';
EXECUTE IMMEDIATE sql_stmt;
END;Snowflake
CREATE OR REPLACE PROCEDURE CONCAT_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let NUM1 = 123;
let NUM2 = 321;
let VAR1 = `value`;
let CONCAT_VAR;
let SQL_STMT;
// ** MSC-WARNING - MSCEWI1038 - THIS STATEMENT MAY BE A DYNAMIC SQL THAT COULD NOT BE RECOGNIZED AND CONVERTED **
CONCAT_VAR = `${concatValue(NUM1)}${concatValue(NUM2)}${concatValue(VAR1)}literal`;
SQL_STMT = `INSERT INTO PUBLIC.t1 VALUES ('${concatValue(CONCAT_VAR)}')`;
EXEC(SQL_STMT);
$$;Logical Operators
Oracle
CREATE OR REPLACE PROCEDURE BOOLEAN_PROC (b_name VARCHAR2, b_value BOOLEAN)
IS
BOOL1 BOOLEAN := FALSE;
x NUMBER := 5;
y NUMBER := NULL;
BEGIN
IF b_value IS NULL THEN
null;
ELSIF b_value = TRUE THEN
null;
ELSIF b_value = TRUE AND b_value = BOOL1 OR b_value = BOOL1 THEN
null;
ELSIF x > y THEN
null;
ELSIF x != y AND x <> y THEN
null;
ELSE
null;
END IF;
END;Snowflake
CREATE OR REPLACE PROCEDURE BOOLEAN_PROC (b_name STRING, b_value BOOLEAN)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let BOOL1 = false;
let X = 5;
let Y = undefined;
if (IS_NULL(B_VALUE)) {
null;
} else if (B_VALUE == true) {
null;
} else if (B_VALUE == true && B_VALUE == BOOL1 || B_VALUE == BOOL1) {
null;
} else if (X > Y) {
null;
} else if (X != Y && X != Y) {
null;
} else {
null;
}
$$;Comparison Operator
Documentation in progress.
IS [NOT] NULL
Oracle
CREATE OR REPLACE PROCEDURE NULL_TEST
IS
NUM1 INTEGER := 789;
BEGIN
IF NUM1 IS NOT NULL THEN
NULL;
END IF;
NUM1 := NULL;
IF NUM1 IS NULL THEN
NULL;
END IF;
END;Snowflake
CREATE OR REPLACE PROCEDURE NULL_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let NUM1 = 789;
if (!IS_NULL(NUM1)) {
null;
}
NUM1 = undefined;
if (IS_NULL(NUM1)) {
null;
}
$$;Like Operator
When there is a LIKE operation, the helper function will be called instead.
Oracle
CREATE OR REPLACE PROCEDURE PROCEDURE_WITH_LIKE AS
BEGIN
IF 'ABC' LIKE '%A%' THEN
null;
END IF;
IF 'ABC' LIKE 'A%' THEN
null;
END IF;
IF 'ABC' NOT LIKE 'D_%' THEN
null;
END IF;
IF 'ABC' NOT LIKE 'D/%%' ESCAPE '/' THEN
null;
END IF;
END;Snowflake
CREATE OR REPLACE PROCEDURE PROCEDURE_WITH_LIKE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
if (LIKE(`ABC`,`%A%`)) {
null;
}
if (LIKE(`ABC`,`A%`)) {
null;
}
if (!LIKE(`ABC`,`D_%`)) {
null;
}
if (!LIKE(`ABC`,`D/%%`,`/`)) {
null;
}
$$;Between Operator
Oracle
CREATE OR REPLACE PROCEDURE BETWEEN_TEST
IS
NUM1 INTEGER := 789;
US INTEGER := 1000;
BEGIN
IF 800 BETWEEN US AND NUM1 THEN
NULL;
END IF;
IF 'BA' BETWEEN 'B' AND 'CA' THEN
NULL;
END IF;
-- Assign null to the variable num1
NUM1 := NULL;
IF (0 BETWEEN NULL AND NUM1) IS NULL THEN
NULL;
END IF;
END;Snowflake
CREATE OR REPLACE PROCEDURE BETWEEN_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let NUM1 = 789;
let US = 1000;
if (BetweenFunc(800,US,NUM1)) {
null;
}
if (BetweenFunc(`BA`,`B`,`CA`)) {
null;
}
// Assign null to the variable num1
NUM1 = undefined;
if (IS_NULL(BetweenFunc(0,undefined,NUM1))) {
null;
}
$$;IN Operator
Oracle
CREATE OR REPLACE PROCEDURE IN_PROC
IS
letter VARCHAR2(1) := 'm';
BEGIN
IF letter IN ('a', 'b', 'c') THEN
null;
ELSE
null;
END IF;
END;Snowflake
CREATE OR REPLACE PROCEDURE IN_PROC ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let LETTER = `m`;
if ([`a`,`b`,`c`].includes(LETTER)) {
null;
} else {
null;
}
$$;
Boolean Expressions
Oracle
CREATE OR REPLACE PROCEDURE BOOLEAN_TEST
IS
done BOOLEAN;
BEGIN
-- These WHILE loops are equivalent
done := FALSE;
WHILE done = FALSE
LOOP
done := TRUE;
END LOOP;
done := FALSE;
WHILE NOT (done = TRUE)
LOOP
done := TRUE;
END LOOP;
done := FALSE;
WHILE NOT done
LOOP
done := TRUE;
END LOOP;
END;Snowflake
CREATE OR REPLACE PROCEDURE BOOLEAN_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let DONE;
// These WHILE loops are equivalent
DONE = false;
while ( DONE == false ) {
DONE = true;
}
DONE = false;
while ( !(DONE == true) ) {
DONE = true;
}
DONE = false;
while ( !DONE ) {
DONE = true;
}
$$;Function Expressions
For Function Expressions inside procedures, they are being converted to the corresponding function or expression in Snowflake. These function calls are passed to an EXEC with a CALL or a SELECT depending on the converted value.
Oracle
CREATE OR REPLACE PROCEDURE FUNCTIONS_TEST(DATEPARAM DATE)
IS
STRING_VALUE VARCHAR(20) := 'HELLO';
BEGIN
STRING_VALUE := TO_CHAR(123);
STRING_VALUE := TO_CHAR(DATEPARAM, 'dd-mm-yyyy', 'NLS_DATE_LANGUAGE = language');
END;Snowflake
CREATE OR REPLACE PROCEDURE FUNCTIONS_TEST (DATEPARAM DATE)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let STRING_VALUE = `HELLO`;
STRING_VALUE = (EXEC(`SELECT TO_CHAR(123)`))[0];
STRING_VALUE = (EXEC(`SELECT
/*** MSC-ERROR - MSCEWI3013 - NLS PARAMETER 'NLS_DATE_LANGUAGE = language' NOT SUPPORTED ***/
TO_CHAR(/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSDATE_UDF' INSERTED. ***/
PUBLIC.JSDATE_UDF(?), 'dd-mm-yyyy')`,[DATEPARAM]))[0];
$$;For more information on the functions transformations check here.
Last updated
Was this helpful?