As is widely acknowledged, non-scalar user-defined functions (UDFs) in Oracle are converted into Snowflake stored procedures to accommodate more intricate functionalities.
This transformation also alters the way the function is invoked, transitioning from a traditional function call to a stored procedure call.
For additional details regarding the invocation of stored procedures, refer to the documentation accessible here: .
Sample Source Patterns
Consider the next function and tables for the examples below.
CREATE OR REPLACE FUNCTION sum_to_varchar_function(p_number1 IN NUMBER, p_number2 IN NUMBER)
RETURN VARCHAR
IS
result VARCHAR(100);
BEGIN
result := TO_CHAR(p_number1 + p_number2);
RETURN result;
END sum_to_varchar_function;
CREATE TABLE example_table (
id NUMBER,
column1 NUMBER
);
INSERT INTO example_table VALUES (1, 15);
CREATE TABLE result_table (
id NUMBER,
result_col VARCHAR(100)
);
CREATE OR REPLACE PROCEDURE sum_to_varchar_function(p_number1 NUMBER(38, 18), p_number2 NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
result VARCHAR(100);
BEGIN
result := TO_CHAR(:p_number1 + :p_number2);
RETURN :result;
END;
$$;
CREATE OR REPLACE TABLE example_table (
id NUMBER(38, 18),
column1 NUMBER(38, 18)
);
INSERT INTO example_table VALUES (1, 15);
CREATE OR REPLACE TABLE result_table (
id NUMBER(38, 18),
result_col VARCHAR(100)
);
UDF Call
Oracle
CREATE OR REPLACE PROCEDURE procedure_calling_function(param1 IN NUMBER)
IS
result_value VARCHAR(200);
BEGIN
result_value := sum_to_varchar_function(3, param1);
INSERT INTO result_table VALUES (1, result_value);
END;
BEGIN
procedure_calling_function(5);
END;
ID RESULT_COL
1 8
Snowflake Scripting
CREATE OR REPLACE PROCEDURE procedure_calling_function (param1 NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
result_value VARCHAR(200);
BEGIN
LET sum_to_varchar_function_TEMP_1 VARCHAR;
sum_to_varchar_function_TEMP_1 := (CALL sum_to_varchar_function(3, :param1));
result_value := :sum_to_varchar_function_TEMP_1;
INSERT INTO result_table VALUES (1, :result_value);
END;
$$;
CALL procedure_calling_function(5);
ID RESULT_COL
1 8
UDF Call within a query
When a function call is embedded within a query, the invocation process becomes more intricate due to Snowflake's limitation of not being able to call procedures directly within queries. To overcome this limitation, the procedure invocation is moved outside the query, and the result is assigned to a variable. This variable is then referenced within the query, thereby achieving functional equivalence. This approach allows for the execution of more complex behaviors within Snowflake queries while adhering to the procedural constraints.
Oracle
CREATE OR REPLACE PROCEDURE procedure_calling_function(param1 IN NUMBER)
IS
result_value VARCHAR(200);
result_value2 VARCHAR(200);
BEGIN
SELECT
sum_to_varchar_function(1, param1) AS result_column,
sum_to_varchar_function(2, param1) AS result_column2
INTO result_value, result_value2
FROM example_table ext;
INSERT INTO result_table VALUES (1, result_value);
INSERT INTO result_table VALUES (2, result_value2);
END;
BEGIN
procedure_calling_function(5);
END;
ID RESULT_COL
1 6
2 7
Snowflake Scripting
CREATE OR REPLACE PROCEDURE procedure_calling_function (param1 NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
result_value VARCHAR(200);
result_value2 VARCHAR(200);
BEGIN
LET sum_to_varchar_function_TEMP_1 VARCHAR;
sum_to_varchar_function_TEMP_1 := (CALL sum_to_varchar_function(1, :param1));
LET sum_to_varchar_function_TEMP_2 VARCHAR;
sum_to_varchar_function_TEMP_2 := (CALL sum_to_varchar_function(2, :param1));
SELECT
:sum_to_varchar_function_TEMP_1 AS result_column,
:sum_to_varchar_function_TEMP_2 AS result_column2
INTO :result_value, :result_value2
FROM example_table ext;
INSERT INTO result_table VALUES (1, :result_value);
INSERT INTO result_table VALUES (2, :result_value2);
END;
$$;
CALL procedure_calling_function(5);
ID RESULT_COL
1 6
2 7
Known Issues
1. Unsupported Usage of UDFs in Queries with Query Dependencies
When calling User-Defined Functions (UDFs) within queries with query dependencies, scenarios involving embedded functions with columns as arguments are not supported. This limitation arises because the column values cannot be accessed from outside the query. Examples of unsupported scenarios include:
BEGIN
SELECT
sum_to_varchar_function(ext.col1, ext.col2) -- columns as arguments not supported
INTO
result_value
FROM example_table ext;
END;
The supported scenarios include function calls with other types of arguments such as literal values, external variables, or parameters. For instance:
BEGIN
SELECT
sum_to_varchar_function(100, param1)
INTO
result_value
FROM example_table ext;
END;
In the supported scenarios, the function can effectively be migrated.
Related EWIs
: User defined function was transformed to a Snowflake procedure.
