CREATE OR REPLACE PROCEDURE CONCAT_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let NUM1 = 123;
let NUM2 = 321;
let VAR1 = `value`;
let CONCAT_VAR;
let SQL_STMT;
CONCAT_VAR = `${concatValue(NUM1)}${concatValue(NUM2)}${concatValue(VAR1)}literal`;
SQL_STMT = `INSERT INTO t1
VALUES ('${concatValue(CONCAT_VAR)}')`;
EXEC(SQL_STMT);
$$;
Logical Operators
Oracle
IN -> Oracle_02.sql
--Additional Params: -t JavaScript
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
OUT -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE BOOLEAN_PROC (b_name STRING, b_value BOOLEAN)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
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
IN -> Oracle_03.sql
--Additional Params: -t JavaScript
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
OUT -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE NULL_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
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
IN -> Oracle_04.sql
--Additional Params: -t JavaScript
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
OUT -> Oracle_04.sql
CREATE OR REPLACE PROCEDURE PROCEDURE_WITH_LIKE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
if (LIKE(`ABC`,`%A%`)) {
null;
}
if (LIKE(`ABC`,`A%`)) {
null;
}
if (!LIKE(`ABC`,`D_%`)) {
null;
}
if (!LIKE(`ABC`,`D/%%`,`/`)) {
null;
}
$$;
Between Operator
Oracle
IN -> Oracle_05.sql
--Additional Params: -t JavaScript
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
OUT -> Oracle_05.sql
CREATE OR REPLACE PROCEDURE BETWEEN_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
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
IN -> Oracle_06.sql
--Additional Params: -t JavaScript
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
OUT -> Oracle_06.sql
CREATE OR REPLACE PROCEDURE IN_PROC ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let LETTER = `m`;
if ([`a`,`b`,`c`].includes(LETTER)) {
null;
} else {
null;
}
$$;
Boolean Expressions
Oracle
IN -> Oracle_07.sql
--Additional Params: -t JavaScript
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
OUT -> Oracle_07.sql
CREATE OR REPLACE PROCEDURE BOOLEAN_TEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
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
IN -> Oracle_08.sql
-- Additional Params: -t JavaScript
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
OUT -> Oracle_08.sql
CREATE OR REPLACE PROCEDURE FUNCTIONS_TEST (DATEPARAM TIMESTAMP)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let STRING_VALUE = `HELLO`;
STRING_VALUE = (EXEC(`SELECT
TO_CHAR(123)`))[0];
STRING_VALUE = (EXEC(`SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0013 - NLS PARAMETER 'NLS_DATE_LANGUAGE = language' NOT SUPPORTED ***/!!!
TO_CHAR(PUBLIC.CAST_DATE_UDF(?), 'dd-mm-yyyy')`,[DATEPARAM]))[0];
$$;
EWIs related
SnowConvert helpers Code removed from the example. You can find them
You might also be interested in
SnowConvert helpers Code removed from the example. You can find them
You might also be interested in
SnowConvert helpers Code removed from the example. You can find them
You might also be interested in
SnowConvert helpers Code removed from the example. You can find them
You may also be interested in
SnowConvert helpers Code removed from the example. You can find them
SnowConvert helpers Code removed from the example. You can find them
SnowConvert helpers Code removed from the example. You can find them
SnowConvert helpers Code removed from the example. You can find them
For more information on the function's transformations check .