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
Create Function
Oracle
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
/*** MSC-WARNING - MSCEWI3053 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/
CREATE OR REPLACE PROCEDURE PUBLIC.FUN1 (PAR1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let VAR1;
let VAR2;
[VAR1] = EXEC(`SELECT COL1 FROM PUBLIC.TABLE1 where col1 = 1`);
VAR2 = `${PAR1}${VAR1}`;
return VAR2;
$$;
Function inside Package
Oracle
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
/*** MSC-WARNING - MSCEWI3053 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/
CREATE OR REPLACE PROCEDURE pkg1.f1 (PAR1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let VAR1;
let VAR2;
[VAR1] = EXEC(`SELECT COL1 FROM PUBLIC.TABLE1 where col1 = 1`);
VAR2 = `${PAR1}${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
CREATE VIEW VIEW1 AS SELECT FUN1(COL2) FROM TABLE1;
CREATE VIEW VIEW2 AS SELECT PKG1.F1(COL1) FROM TABLE1;
Snowflake
CREATE OR REPLACE VIEW PUBLIC.VIEW1 AS SELECT
/*** MSC-ERROR - MSCEWI3050 - UDF WAS TRASFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE A QUERY IS NOT SUPPORTED ***/
PUBLIC.FUN1_UDF('FUN1(COL2)') FROM PUBLIC.TABLE1;
CREATE OR REPLACE VIEW PUBLIC.VIEW2 AS SELECT
/*** MSC-ERROR - MSCEWI3050 - UDF WAS TRASFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE A QUERY IS NOT SUPPORTED ***/
PUBLIC.PKG1.F1_UDF('PKG1.F1(COL1)') FROM PUBLIC.TABLE1;
Inside other functions or stored procedures
Oracle
The functions that are converted to procedures are called using the EXEC Snowflake helper.
Oracle
VAR1 := FUN1(pkg1.f1(X, FUN1(10)));
Snowflake:
VAR1 = (EXEC(`CALL PUBLIC.FUN1(?)`,[(EXEC(`CALL pkg1.f1(?, ?)`,[(EXEC(`CALL PUBLIC.FUN1(10)`,[X]))[0]]))[0]]))[0];
Oracle
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
/*** MSC-WARNING - MSCEWI3053 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/
CREATE OR REPLACE PROCEDURE PUBLIC.FUN1 (x FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let VAR1;
VAR1 = (EXEC(`CALL PUBLIC.FUN2(?)`,[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
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
/*** MSC-WARNING - MSCEWI3053 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE ***/
CREATE OR REPLACE PROCEDURE FUN1 ()
RETURNS FLOAT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let VAR1;
VAR1 = VAR1 + 1;
// ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
EXEC(`INSERT INTO PUBLIC.TABLE1(col1, col2) VALUES(?, ?)`,[X,VAR1]);
// ** MSC-WARNING - MSCEWI1022 - 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
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
CREATE OR REPLACE FUNCTION PUBLIC.FUN1 (PAR1 STRING)
RETURNS STRING
AS
$$
SELECT COL1 FROM PUBLIC.TABLE1 where col1 = PAR1
$$;
Functions with only logic
UDFs that do not use any SQL statement are converted into Snowflake JavaScript UDFs.
Oracle
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
CREATE OR REPLACE FUNCTION PUBLIC.FUN1(x FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
// REGION SnowConvert Helpers Code
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
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
CREATE OR REPLACE PROCEDURE FUN1 (x FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
let VAR1;
// ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
[VAR1] = EXEC(`SELECT COL1 FROM PUBLIC.TABLE1 WHERE ID = ?`,[X]);
if (VAR1 < 5) {
VAR1 = 1;
} else {
VAR1 = 0;
}
// ** MSC-WARNING - MSCEWI1022 - 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
Oracle
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
CREATE OR REPLACE PROCEDURE PUBLIC.FUN1 (x FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
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
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
CREATE OR REPLACE FUNCTION FUN1 (flag FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
// REGION SnowConvert Helpers Code
return FLAG == 1 && `one` || (FLAG == 2 && `two` || (FLAG == 3 && `three` || (FLAG == 4 && `four` || `unknown`)));
$$;
Last updated
Was this helpful?