User defined functions

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.

Since functions are being transformed into procedures, the transformation reference for PL/SQL also applies here.

Translation

Some parts in the output code are omitted for clarity reasons.

Create Function

Oracle

IN -> Oracle_01.sql

--Additional Params: -t JavaScript
CREATE OR REPLACE FUNCTION FUN1(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;

Snowflake

OUT -> Oracle_01.sql

CREATE OR REPLACE FUNCTION FUN1 (PAR1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = '{ "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
                TABLE1
            where 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 JavaScript
CREATE 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 VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = '{ "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
        TABLE1
      where col1 = 1) AS VAR1,
      NVL(PAR1 :: STRING, '') || NVL(VAR1 :: STRING, '') AS
      VAR2
  )
  SELECT
    VAR2
  FROM
    declaration_variables_cte1
$$;

Return data type mapping

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

CREATE VIEW VIEW1 AS SELECT FUN1(COL2) FROM TABLE1;
CREATE VIEW VIEW2 AS SELECT PKG1.F1(COL1) FROM TABLE1;

Snowflake

OUT -> Oracle_03.sql

CREATE OR REPLACE VIEW VIEW1
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
SELECT FUN1(COL2) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FUN1' NODE ***/!!! FROM
TABLE1;

CREATE OR REPLACE VIEW VIEW2
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
SELECT 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 JavaScript
CREATE 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