This is a translation reference to convert Oracle functions to snowflake.
General Description
Most Oracle UDFs and UDFs inside packages, are being transformed to Snowflake Stored Procedures, to maintain functional equivalence, due to Snowflake UDFs having some limitations executing DML (Data Manipulation Language) statements.
Some parts in the output code are omitted for clarity reasons.
Create Function
Oracle
IN -> Oracle_01.sql
--Additional Params: -t JavaScriptCREATE OR REPLACE FUNCTION FUN1(PAR1 VARCHAR)RETURN VARCHARIS VAR1 VARCHAR(20); VAR2 VARCHAR(20);BEGIN SELECT COL1 INTO VAR1 FROM TABLE1 where col1 =1; VAR2 := PAR1 || VAR1; RETURN VAR2 ;END;
Snowflake
OUT -> Oracle_01.sql
CREATE OR REPLACE FUNCTION FUN1 (PAR1 VARCHAR)RETURNS VARCHARLANGUAGE SQLCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "11/13/2024", "domain": "test" }}'AS$$ WITH declaration_variables_cte1 AS ( SELECT ( SELECT COL1 FROM TABLE1where col1 =1) AS VAR1, NVL(PAR1 :: STRING, '') || NVL(VAR1 :: STRING, '') AS VAR2 ) SELECT VAR2 FROM declaration_variables_cte1$$;
Function inside Package
Oracle
IN -> Oracle_02.sql
--Additional Params: -t JavaScriptCREATE OR REPLACE PACKAGE BODY pkg1 AS FUNCTION f1(PAR1 VARCHAR) RETURN VARCHAR IS VAR1 VARCHAR(20); VAR2 VARCHAR(20); BEGIN SELECT COL1 INTO VAR1 FROM TABLE1 where col1 =1; VAR2 := PAR1 || VAR1; RETURN VAR2 ; END f1;END pkg1;
Snowflake
OUT -> Oracle_02.sql
CREATE OR REPLACE FUNCTION pkg1.f1(PAR1 VARCHAR)RETURNS VARCHARLANGUAGE SQLCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "11/13/2024", "domain": "test" }}'AS$$ WITH declaration_variables_cte1 AS ( SELECT ( SELECT COL1 FROM TABLE1where col1 =1) AS VAR1, NVL(PAR1 :: STRING, '') || NVL(VAR1 :: STRING, '') AS VAR2 ) SELECT VAR2 FROM declaration_variables_cte1$$;
Return data type mapping
Oracle PL SQL type
Snowflake equivalent
NUMBER
FLOAT
LONG
VARCHAR
VARCHAR2
STRING
BLOB
BINARY
BFILE
BINARY
Call
Inside queries
Calls of functions that were transformed to procedures inside queries are converted into a an empty Snowflake JavaScript UDF. This Snowflake UDF is generated in the STUB_UDF.sql file inside the UDF Helpers directory.
Oracle
IN -> Oracle_03.sql
CREATEVIEWVIEW1AS SELECT FUN1(COL2) FROM TABLE1;CREATEVIEWVIEW2AS SELECT PKG1.F1(COL1) FROM TABLE1;
The functions that are converted to procedures are called using the EXEC Snowflake helper.
Oracle
IN -> Oracle_04.sql
--Additional Params: -t JavaScriptCREATE OR REPLACE FUNCTION FUN1(x NUMBER) RETURN NUMBER IS VAR1 NUMBER; BEGIN-- FUN2 is another UDF VAR1 := FUN2(pkg1.f1(X, FUN2(10))); RETURN VAR1; END f1;
Snowflake:
OUT -> Oracle_04.sql
CREATE OR REPLACE FUNCTION FUN1 (x NUMBER(38, 18))RETURNS NUMBER(38, 18)LANGUAGE SQLCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "11/13/2024", "domain": "test" }}'AS$$ WITH declaration_variables_cte1 AS ( SELECT FUN2(pkg1.f1(X, FUN2(10) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FUN2' NODE ***/!!!) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'pkg1.f1' NODE ***/!!!) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FUN2' NODE ***/!!! AS-- FUN2 is another UDF VAR1 ) SELECT VAR1 FROM declaration_variables_cte1$$;
Oracle
IN -> Oracle_05.sql
--Additional Params: -t JavaScriptCREATE OR REPLACE FUNCTION FUN1(x NUMBER) RETURN NUMBER IS VAR1 NUMBER; BEGIN-- FUN2 is another UDF VAR1 := FUN2(X); RETURN VAR1; END f1;
Snowflake
OUT -> Oracle_05.sql
CREATE OR REPLACE FUNCTION FUN1 (x NUMBER(38, 18))RETURNS NUMBER(38, 18)LANGUAGE SQLCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "11/13/2024", "domain": "test" }}'AS$$ WITH declaration_variables_cte1 AS ( SELECT FUN2(X) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FUN2' NODE ***/!!! AS-- FUN2 is another UDF VAR1 ) SELECT VAR1 FROM declaration_variables_cte1$$;
Different cases and limitations
Functions with DMLs
These functions cannot be executed in queries in Oracle, so their usage wont be limited when transforming them to Snowflake Procedures.
Oracle
IN -> Oracle_06.sql
--Additional Params: -t JavaScriptCREATE OR REPLACE FUNCTION FUN1(x NUMBER)RETURN NUMBER ISVAR1 NUMBER;BEGIN VAR1 := VAR1 +1; INSERT INTO TABLE1(col1, col2) VALUES(X, VAR1); UPDATE TABLE2 SET COL1 = VAR1 WHERE ID = X; RETURN VAR1;END FUN1;
Snowflake
OUT -> Oracle_06.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATE OR REPLACE PROCEDURE FUN1 (x FLOAT)RETURNS FLOATLANGUAGE JAVASCRIPTCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$// SnowConvert Helpers Code section is omitted. let VAR1; VAR1 = VAR1 +1;//** SSC-EWI-0022- ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. ** EXEC(`INSERT INTO TABLE1(col1, col2) VALUES(?, ?)`,[X,VAR1]);//** SSC-EWI-0022- ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. ** EXEC(`UPDATE TABLE2 SET COL1 = ? WHERE ID = ?`,[VAR1,X]);return VAR1;$$;
Functions with only one SELECT INTO
These functions are transformed to Snowflake SQL functions by removing the INTO part of the select.
Oracle
IN -> Oracle_07.sql
CREATE OR REPLACE FUNCTION FUN1(PAR1 VARCHAR)RETURN VARCHARIS VAR1 VARCHAR(20);BEGIN SELECT COL1 INTO VAR1 FROM TABLE1 where col1 = PAR1; RETURN VAR1;END;
Snowflake
OUT -> Oracle_07.sql
CREATE OR REPLACE FUNCTION FUN1 (PAR1 VARCHAR)RETURNS VARCHARLANGUAGE SQLCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "11/13/2024", "domain": "test" }}'AS$$ WITH declaration_variables_cte1 AS ( SELECT ( SELECT COL1 FROM TABLE1where col1 = PAR1) AS VAR1 ) SELECT VAR1 FROM declaration_variables_cte1$$;
Functions with only logic
UDFs that do not use any SQL statement are converted into Snowflake JavaScript UDFs.
When SQL built-in functions are included in the logic the user defined function is converted to a Snowflake procedure. Translation for built in functions to a JavaScript equivalent is planned to be delivered in the future.
Examples for built-in functions: UPPER(), TRIM(), ABS().
Oracle
IN -> Oracle_08.sql
--Additional Params: -t JavaScriptCREATE OR REPLACE FUNCTION FUN1(x NUMBER)RETURN NUMBER ISVAR1 NUMBER;BEGIN IF x <5 THEN VAR1 :=1; ELSE VAR1 :=0; END IF; RETURN VAR1;END FUNC01;
Snowflake
OUT -> Oracle_08.sql
CREATE OR REPLACE FUNCTION FUN1 (x NUMBER(38, 18))RETURNS NUMBER(38, 18)LANGUAGE SQLCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "11/13/2024", "domain": "test" }}'AS$$ WITH declaration_variables_cte1 AS ( SELECT CASE WHEN x <5 THEN 1 ELSE 0 END AS VAR1 ) SELECT VAR1 FROM declaration_variables_cte1$$;
Functions with more than one SQL statement
UDFs transformed into procedures cannot be called from a query.
Oracle
IN -> Oracle_09.sql
--Additional Params: -t JavaScriptCREATE OR REPLACE FUNCTION FUN1(x NUMBER)RETURN NUMBER ISVAR1 NUMBER;BEGIN SELECT COL1 INTO VAR1 FROM TABLE1 WHERE ID = X; IF VAR1 <5 THEN VAR1 :=1; ELSE VAR1 :=0; END IF; UPDATE TABLE1 SET COL1 = VAR1 WHERE ID = X; RETURN VAR1;END FUN1;
Snowflake
OUT -> Oracle_09.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATE OR REPLACE PROCEDURE FUN1 (x FLOAT)RETURNS FLOATLANGUAGE JAVASCRIPTCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$// SnowConvert Helpers Code section is omitted. let VAR1;//** SSC-EWI-0022- ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. ** [VAR1] = EXEC(`SELECT COL1FROM TABLE1WHERE ID = ?`,[X]);if (VAR1 <5) { VAR1 =1; } else { VAR1 =0; }//** SSC-EWI-0022- ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. ** EXEC(`UPDATE TABLE1 SET COL1 = ? WHERE ID = ?`,[VAR1,X]);return VAR1;$$;
Functions with only logic and built-in SQL functions
This transformation is planned to be delivery in the future, currently all functions are being transformed to stored procedures.
Oracle
IN -> Oracle_10.sql
--Additional Params: -t JavaScriptCREATE OR REPLACE FUNCTION FUN1(x FLOAT)RETURN NUMBER ISVAR1 NUMBER;BEGIN IF TRUNC(X) <5 THEN VAR1 :=1; ELSE VAR1 :=0; END IF; RETURN VAR1;END FUNC01;
Current transformation to Snowflake
OUT -> Oracle_10.sql
CREATE OR REPLACE FUNCTION FUN1 (x FLOAT)RETURNS NUMBER(38, 18)LANGUAGE SQLCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "11/13/2024", "domain": "test" }}'AS$$ WITH declaration_variables_cte1 AS ( SELECT CASE WHEN TRUNC(X) <5 THEN 1 ELSE 0 END AS VAR1 ) SELECT VAR1 FROM declaration_variables_cte1$$;
RETURN CASE
The transformation is the same transformation when the CASE is use to assign a variable. You can check the transformation of CASE in the PL/SQL section.
Oracle
IN -> Oracle_11.sql
--Additional Params: -t JavaScriptCREATE OR REPLACE FUNCTION FUN1 (flag FLOAT)RETURN NUMBER ISBEGINreturn CASE flag WHEN 1 THEN 'one' WHEN 2 THEN 'two' WHEN 3 THEN 'three' WHEN 4 THEN 'four' ELSE 'unknown' END;END FUN1;
SnowFlake
OUT -> Oracle_11.sql
CREATE OR REPLACE FUNCTION FUN1 (flag FLOAT)RETURNS NUMBER(38, 18)LANGUAGE SQLCOMMENT ='{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "11/13/2024", "domain": "test" }}'AS$$ SELECT CASE flag WHEN 1 THEN 'one' WHEN 2 THEN 'two' WHEN 3 THEN 'three' WHEN 4 THEN 'four' ELSE 'unknown' END$$;
Known Issues
No issues were found.
Related EWIs
SSC-EWI-0022: One or more identifiers in this statement were considered parameters by default.