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 REPLACEFUNCTIONFUN1(PAR1 VARCHAR)RETURNVARCHARIS VAR1 VARCHAR(20); VAR2 VARCHAR(20);BEGINSELECT COL1 INTO VAR1 FROM TABLE1 where col1 =1; VAR2 := PAR1 || VAR1;RETURN VAR2 ;END;
Snowflake
OUT -> Oracle_01.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATEORREPLACEPROCEDURE FUN1 (PAR1 STRING)RETURNS STRINGLANGUAGE JAVASCRIPTCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTEASCALLERAS$$// SnowConvert Helpers Code section is omitted. let VAR1; let VAR2; [VAR1] =EXEC(`SELECT COL1FROM TABLE1where col1 = 1`); VAR2 =`${concatValue(PAR1)}${concatValue(VAR1)}`;return VAR2;$$;
Function inside Package
Oracle
IN -> Oracle_02.sql
--Additional Params: -t JavaScriptCREATEORREPLACE PACKAGE BODY pkg1 ASFUNCTION f1(PAR1 VARCHAR) RETURNVARCHARIS VAR1 VARCHAR(20); VAR2 VARCHAR(20);BEGINSELECT COL1 INTO VAR1 FROM TABLE1 where col1 =1; VAR2 := PAR1 || VAR1;RETURN VAR2 ;END f1;END pkg1;
Snowflake
OUT -> Oracle_02.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATEORREPLACEPROCEDURE pkg1.f1(PAR1 STRING)RETURNS STRINGLANGUAGE JAVASCRIPTCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTEASCALLERAS$$// SnowConvert Helpers Code section is omitted. let VAR1; let VAR2; [VAR1] =EXEC(`SELECT COL1FROM TABLE1where col1 = 1`); VAR2 =`${concatValue(PAR1)}${concatValue(VAR1)}`;return VAR2;$$;
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
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<