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.
IN -> Oracle_01.sql
CREATE OR REPLACEFUNCTIONsum_to_varchar_function(p_number1 INNUMBER, p_number2 INNUMBER)RETURNVARCHARIS result VARCHAR(100);BEGIN result := TO_CHAR(p_number1 + p_number2);RETURN result;END sum_to_varchar_function;CREATETABLEexample_table ( id NUMBER, column1 NUMBER);INSERT INTO example_table VALUES (1, 15);CREATETABLEresult_table ( id NUMBER, result_col VARCHAR(100));
OUT -> Oracle_01.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATEORREPLACEPROCEDURE sum_to_varchar_function(p_number1 NUMBER(38, 18), p_number2 NUMBER(38, 18))RETURNSVARCHARLANGUAGESQLCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTEASCALLERAS$$DECLARE result VARCHAR(100);BEGIN result := TO_CHAR(:p_number1 + :p_number2);RETURN :result;END;$$;CREATE OR REPLACETABLEexample_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_tableVALUES (1, 15);CREATE OR REPLACETABLEresult_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"}}'
;
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
IN -> Oracle_03.sql
CREATEORREPLACEPROCEDURE procedure_calling_function(param1 INNUMBER)IS result_value VARCHAR(200); result_value2 VARCHAR(200);BEGINSELECT sum_to_varchar_function(1, param1) AS result_column, sum_to_varchar_function(2, param1) AS