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.
The next samples will create a table, and attempt to drop the table using Execute Immediate.
Using a hard-coded string
Oracle
IN -> Oracle_01.sql
CREATE TABLE immediate_dropped_table( col1 INTEGER);CREATE OR REPLACE PROCEDURE dropping_procedureAS BEGIN EXECUTE IMMEDIATE 'DROP TABLE immediate_dropped_table PURGE';END;CALL dropping_procedure();SELECT * FROM immediate_dropped_table;
Snowflake Scripting
OUT -> Oracle_01.sql
CREATE OR REPLACE TABLE immediate_dropped_table ( col1 INTEGER)COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';CREATE OR REPLACE PROCEDURE dropping_procedure ()RETURNS VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ BEGIN !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!! EXECUTE IMMEDIATE 'DROP TABLE immediate_dropped_table'; END;$$;CALL dropping_procedure();SELECT * FROM immediate_dropped_table;
Storing the string in a variable
Oracle
IN -> Oracle_02.sql
CREATE TABLE immediate_dropped_table( col1 INTEGER);CREATE OR REPLACE PROCEDURE dropping_procedureASBEGIN DECLARE statement_variable VARCHAR2(500) :='DROP TABLE immediate_dropped_table PURGE'; BEGIN EXECUTE IMMEDIATE statement_variable; END;END;CALL dropping_procedure();SELECT * FROM immediate_dropped_table;
Snowflake Scripting
OUT -> Oracle_02.sql
CREATE OR REPLACE TABLE immediate_dropped_table ( col1 INTEGER)COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';CREATE OR REPLACE PROCEDURE dropping_procedure ()RETURNS VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ BEGIN DECLARE statement_variable VARCHAR(500) :='DROP TABLE immediate_dropped_table'; BEGIN !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!! EXECUTE IMMEDIATE :statement_variable; END; END;$$;CALL dropping_procedure();SELECT * FROM immediate_dropped_table;
Concatenation for parameters in dynamic statement
Oracle
IN -> Oracle_03.sql
CREATE TABLE immediate_dropped_table( col1 INTEGER);CREATE OR REPLACE PROCEDURE dropping_procedure(param1 VARCHAR2)ASBEGIN DECLARE statement_variable VARCHAR2(500) :='DROP TABLE '|| param1 ||' PURGE'; BEGIN EXECUTE IMMEDIATE statement_variable; END;END;CALL dropping_procedure();SELECT * FROM immediate_dropped_table;
Snowflake Scripting
OUT -> Oracle_03.sql
CREATE OR REPLACE TABLE immediate_dropped_table ( col1 INTEGER)COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';CREATE OR REPLACE PROCEDURE dropping_procedure (param1 VARCHAR)RETURNS VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ BEGIN DECLARE statement_variable VARCHAR(500) :='DROP TABLE '|| NVL(:param1 :: STRING, ''); BEGIN !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!! EXECUTE IMMEDIATE :statement_variable; END; END;$$;CALL dropping_procedure();SELECT * FROM immediate_dropped_table;
USING Clause transformation
Oracle
IN -> Oracle_04.sql
CREATE TABLE immediate_inserted_table(COL1 INTEGER);CREATE OR REPLACE PROCEDURE inserting_procedure_using(param1 INTEGER)ASBEGIN EXECUTE IMMEDIATE 'INSERT INTO immediate_inserted_table VALUES (:1)' USING param1;END;CALL inserting_procedure_using(1);SELECT * FROM immediate_inserted_table;
COL1|
----+
1|
Snowflake Scripting
Please note parenthesis are required for parameters in the USING Clause in Snowflake Scripting.
OUT -> Oracle_04.sql
CREATE OR REPLACE TABLE immediate_inserted_table (COL1 INTEGER)COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';CREATE OR REPLACE PROCEDURE inserting_procedure_using (param1 INTEGER)RETURNS VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ BEGIN !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!! EXECUTE IMMEDIATE 'INSERT INTO immediate_inserted_tableVALUES (?)' USING ( param1); END;$$;CALL inserting_procedure_using(1);SELECT * FROM immediate_inserted_table;
COL1|
----+
1|
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
IN -> Oracle_05.sql
CREATE OR REPLACE PROCEDURE inserting_procedure_variable_execute_concatenation_parameter(param1 INTEGER)IS query VARCHAR2(500) :='INSERT INTO immediate_inserted_table VALUES (';BEGIN EXECUTE IMMEDIATE query || param1 ||')';END;
Snowflake Scripting
Please note parenthesis are required for parameters in the USING Clause in Snowflake Scripting.
OUT -> Oracle_05.sql
CREATE OR REPLACE PROCEDURE inserting_procedure_variable_execute_concatenation_parameter (param1 INTEGER)RETURNS VARCHARLANGUAGE SQLEXECUTE AS CALLERAS$$ DECLARE query VARCHAR(500) :='INSERT INTO immediate_inserted_table VALUES ('; BEGIN !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!! !!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!! EXECUTE IMMEDIATE NVL(:query :: STRING, '') || NVL(:param1 :: STRING, '') ||')'; END;$$;