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.
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
CREATEVIEWVIEW1ASSELECT FUN1(COL2) FROM TABLE1;CREATEVIEWVIEW2ASSELECT PKG1.F1(COL1) FROM TABLE1;
Snowflake
OUT -> Oracle_03.sql
CREATE OR REPLACEVIEWVIEW1COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'ASSELECT FUN1(COL2) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FUN1' NODE ***/!!! FROMTABLE1;CREATE OR REPLACEVIEWVIEW2COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'ASSELECT PKG1.F1(COL1) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PKG1.F1' NODE ***/!!! FROMTABLE1;
Inside other functions or stored procedures
Oracle
The functions that are converted to procedures are called using the EXEC Snowflake helper.
Oracle
IN -> Oracle_04.sql
--Additional Params: -t JavaScriptCREATE OR REPLACEFUNCTIONFUN1(x NUMBER) RETURNNUMBERIS VAR1 NUMBER;BEGIN-- FUN2 is another UDF VAR1 := FUN2(pkg1.f1(X, FUN2(10)));RETURN VAR1;END f1;
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 REPLACEFUNCTIONFUN1(x NUMBER)RETURNNUMBERISVAR1 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 **CREATEORREPLACEPROCEDURE FUN1 (x FLOAT)RETURNSFLOATLANGUAGE JAVASCRIPTCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTEASCALLERAS$$// SnowConvert Helpers Code section is omitted. let VAR1; VAR1 = VAR1 +1;//** SSC-EWI-0022- ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BYDEFAULT. REFERENCED TABLENOT FOUND. **EXEC(`INSERT INTO TABLE1(col1, col2) VALUES(?, ?)`,[X,VAR1]);//** SSC-EWI-0022- ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BYDEFAULT. REFERENCED TABLENOT 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 REPLACEFUNCTIONFUN1(PAR1 VARCHAR)RETURNVARCHARIS VAR1 VARCHAR(20);BEGINSELECT COL1 INTO VAR1 FROM TABLE1 where col1 = PAR1;RETURN VAR1;END;
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 REPLACEFUNCTIONFUN1(x NUMBER)RETURNNUMBERISVAR1 NUMBER;BEGINIF x <5THEN VAR1 :=1; ELSE VAR1 :=0;ENDIF;RETURN VAR1;END FUNC01;
UDFs transformed into procedures cannot be called from a query.
Oracle
IN -> Oracle_09.sql
--Additional Params: -t JavaScriptCREATE OR REPLACEFUNCTIONFUN1(x NUMBER)RETURNNUMBERISVAR1 NUMBER;BEGINSELECT COL1 INTO VAR1 FROM TABLE1 WHERE ID = X;IF VAR1 <5THEN VAR1 :=1; ELSE VAR1 :=0;ENDIF;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 **CREATEORREPLACEPROCEDURE FUN1 (x FLOAT)RETURNSFLOATLANGUAGE JAVASCRIPTCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTEASCALLERAS$$// SnowConvert Helpers Code section is omitted. let VAR1;//** SSC-EWI-0022- ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BYDEFAULT. REFERENCED TABLENOT 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 BYDEFAULT. REFERENCED TABLENOT 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.
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 REPLACEFUNCTIONFUN1 (flag FLOAT)RETURNNUMBERISBEGINreturnCASE flagWHEN1THEN'one'WHEN2THEN'two'WHEN3THEN'three'WHEN4THEN'four'ELSE'unknown'END;END FUN1;