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

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FUN1 (PAR1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.

    let VAR1;
    let VAR2;
    [VAR1] = EXEC(`SELECT
   COL1
FROM
   TABLE1
where col1 = 1`);
    VAR2 = `${concatValue(PAR1)}${concatValue(VAR1)}`;
    return VAR2;
$$;

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

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE pkg1.f1(PAR1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  // SnowConvert Helpers Code section is omitted.

  let VAR1;
  let VAR2;
  [VAR1] = EXEC(`SELECT
   COL1
FROM
   TABLE1
where 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

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

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FUN1 (x FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  // SnowConvert Helpers Code section is omitted.

  let VAR1;
      // FUN2 is another UDF
  VAR1 = (EXEC(`SELECT
   FUN2(pkg1.f1(?, 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 ***/!!!`,[X]))[0];
  return VAR1;
$$;

Oracle

IN -> Oracle_05.sql

--Additional Params: -t JavaScript

CREATE 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

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FUN1 (x FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  // SnowConvert Helpers Code section is omitted.

  let VAR1;
      // FUN2 is another UDF
  VAR1 = (EXEC(`SELECT
   FUN2(?) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FUN2' NODE ***/!!!`,[X]))[0];
  return VAR1;
$$;

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 JavaScript

CREATE OR REPLACE FUNCTION FUN1(x NUMBER)
RETURN NUMBER IS
VAR1 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

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FUN1 (x FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    // 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 VARCHAR
IS
    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 VARCHAR
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
$$
    SELECT
        COL1
    FROM
        TABLE1
    where col1 = PAR1
$$;

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 JavaScript

CREATE OR REPLACE FUNCTION FUN1(x NUMBER)
RETURN NUMBER IS
VAR1 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 FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
$$
    // SnowConvert Helpers Code section is omitted.

    let VAR1;
    if (X < 5) {
        VAR1 = 1;
    } else {
        VAR1 = 0;
    }
    return VAR1;
$$;

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 JavaScript

CREATE OR REPLACE FUNCTION FUN1(x NUMBER)
RETURN NUMBER IS
VAR1 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

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FUN1 (x FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    // 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
   COL1
FROM
   TABLE1
WHERE 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 JavaScript

CREATE OR REPLACE FUNCTION FUN1(x FLOAT)
RETURN NUMBER IS
VAR1 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

!!!RESOLVE EWI!!! /*** SSC-EWI-0068 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/!!!
CREATE OR REPLACE PROCEDURE FUN1 (x FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.

    let VAR1;
    if ((EXEC(`SELECT
   TRUNC(?)`,[X]))[0] < 5) {
        VAR1 = 1;
    } else {
        VAR1 = 0;
    }
    return VAR1;
$$;

Transformation planned to be delivered in the future

CREATE OR REPLACE FUNCTION FUN1(x FLOAT)
RETURNS NUMBER
LANGUAGE JAVASCRIPT
AS
$$
  let VAR1;
   if (TRUNC_EQUIVALENT(X) < 5) {
      VAR1 = 1;
   } else {
      VAR1 = 0;
   }
   return VAR1;
$$;

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 JavaScript

CREATE OR REPLACE FUNCTION FUN1 (flag FLOAT)
RETURN NUMBER IS
BEGIN
  return 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 FLOAT
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
AS
$$
	// SnowConvert Helpers Code section is omitted.

	return FLAG == 1 && `one` || (FLAG == 2 && `two` || (FLAG == 3 && `three` || (FLAG == 4 && `four` || `unknown`)));
$$;

Related EWIs

1. SSC-EWI-0022: One or more identifiers in this statement were considered parameters by default.

2. SSC-EWI-0068: User defined function was transformed to a Snowflake procedure.

3. SSC-EWI-0073: Pending Functional Equivalence Review.