UDF CALL
Translation reference for User-defined function (UDF) Call
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
UDF Call
Oracle
Snowflake Scripting
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
Snowflake Scripting
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:
The supported scenarios include function calls with other types of arguments such as literal values, external variables, or parameters. For instance:
In the supported scenarios, the function can effectively be migrated.
Related EWIs
SSC-EWI-0073: Pending Functional Equivalence Review.
SSC-FDM-0006: Number type column may not behave similarly in Snowflake.
SSC-FDM-0029: User defined function was transformed to a Snowflake procedure.
Last updated