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 ***/!!! FROM
TABLE1;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 ***/!!! FROM
TABLE1;
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;