EXECUTE IMMEDIATE
Translation reference to convert Oracle EXECUTE IMMEDIATE statement to Snowflake Scripting
Description
The
EXECUTE
IMMEDIATE
statement builds and runs a dynamic SQL statement in a single operation.Native dynamic SQL uses the
EXECUTE
IMMEDIATE
statement to process most dynamic SQL statements. (Oracle PL/SQL Language Reference EXECUTE IMMEDIATE Statement)
Snowflake Scripting has support for this statement, albeit with some functional differences. For more information on the Snowflake counterpart, please visit Snowflake's EXECUTE IMMEDIATE documentation.
Sample Source Patterns
The next samples will create a table, and attempt to drop the table using Execute Immediate.
Using a hard-coded string
Oracle
Snowflake Scripting
Storing the string in a variable
Oracle
Snowflake Scripting
Concatenation for parameters in dynamic statement
Oracle
Snowflake Scripting
USING Clause transformation
Oracle
Snowflake Scripting
Known Issues
1. Immediate Execution results cannot be stored in variables.
SnowScripting does not support INTO nor BULK COLLECT INTO clauses. For this reason, results will need to be passed through other means.
2. Numeric Placeholders
Numeric Names for placeholders are currently not being recognized by SnowConvert, but there is a work item to fix this issue.
3. Argument Expressions are not supported by Snowflake Scripting
In Oracle it is possible to use Expressions as Arguments for the Using Clause; however, this is not supported by Snowflake Scripting, and they are commented out.
4. Dynamic SQL Execution queries may be marked incorrectly as non-runnable.
In some scenarios there an execute statement may be commented regardless of being safe or non-safe to run so please take this into account:
Oracle
Snowflake Scripting
Related EWIs
SSC-EWI-0027: Variable with invalid query.
SSC-EWI-0030: The statement below has usages of dynamic SQL.
Last updated