UDF CALL

Translation reference for User-defined function (UDF) Call

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

Description

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: PROCEDURE CALL.

Sample Source Patterns

Consider the next function and tables for the examples below.

Oracle

IN -> Oracle_01.sql

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)
);

Snowflake

OUT -> Oracle_01.sql

--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE sum_to_varchar_function(p_number1 NUMBER(38, 18), p_number2 NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
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) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
       column1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
   )
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

INSERT INTO example_table
VALUES (1, 15);

CREATE OR REPLACE TABLE result_table (
    id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
       result_col VARCHAR(100)
   )
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

UDF Call

Oracle

Query

IN -> Oracle_02.sql

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;

Result

ID	RESULT_COL
1	8

Snowflake Scripting

Query

OUT -> Oracle_02.sql

CREATE OR REPLACE PROCEDURE procedure_calling_function (param1 NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        result_value VARCHAR(200);
    BEGIN
        result_value := sum_to_varchar_function(3, :param1) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'sum_to_varchar_function' NODE ***/!!!;
        INSERT INTO result_table
        VALUES (1, :result_value);
    END;
$$;

DECLARE
    call_results VARIANT;

    BEGIN
    CALL
    procedure_calling_function(5);
    RETURN call_results;
    END;

Result

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

Query

IN -> Oracle_03.sql

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