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)

Oracle EXECUTE IMMEDIATE Syntax

EXECUTE IMMEDIATE <dynamic statement> [<additional clause> , ...];

dynamic statement::= { '<string literal>' | <variable> }

additional clauses::=
{ <into clause> [<using clause>]
| <bulk collect into clause> [<using clause>]
| <using clause> [<dynamic return clause>]
| <dynamic return clasue> }

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.

Snow Scripting EXECUTE IMMEDIATE Syntax

EXECUTE IMMEDIATE <dynamic statement> ;

dynamic statement::= {'<string literal>' | <variable> | $<session variable>}

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

Query

CREATE TABLE immediate_dropped_table(
    col1 INTEGER
);

CREATE OR REPLACE PROCEDURE dropping_procedure
AS BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE immediate_dropped_table PURGE';
END;

CALL dropping_procedure();
SELECT * FROM immediate_dropped_table;

Snowflake Scripting

Query

CREATE OR REPLACE TABLE immediate_dropped_table (
        col1 INTEGER
);

CREATE OR REPLACE PROCEDURE dropping_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
        EXECUTE IMMEDIATE '/*** MSC-WARNING - MSCEWI3084 - PURGE REMOVED FROM DROP TABLE STATEMENT. ***/
DROP TABLE immediate_dropped_table';
    END;
$$;

CALL dropping_procedure();

SELECT * FROM
    immediate_dropped_table;

Storing the string in a variable

Oracle

Query

CREATE TABLE immediate_dropped_table(
    col1 INTEGER
);

CREATE OR REPLACE PROCEDURE dropping_procedure
AS
BEGIN
    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

Query

CREATE OR REPLACE TABLE immediate_dropped_table (
    col1 INTEGER
);

CREATE OR REPLACE PROCEDURE dropping_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        DECLARE
            statement_variable VARCHAR(500) /*** MSC-WARNING - MSCEWI1036 - VARCHAR2 DATA TYPE CONVERTED TO VARCHAR ***/ := '/*** MSC-WARNING - MSCEWI3084 - PURGE REMOVED FROM DROP TABLE STATEMENT. ***/
DROP TABLE immediate_dropped_table';
        BEGIN
            --** MSC-ERROR - MSCCP0004 - 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

Query

CREATE TABLE immediate_dropped_table(
    col1 INTEGER
);

CREATE OR REPLACE PROCEDURE dropping_procedure(param1 VARCHAR2)
AS
BEGIN
    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

Query

CREATE OR REPLACE TABLE immediate_dropped_table (
    col1 INTEGER
);

CREATE OR REPLACE PROCEDURE dropping_procedure (param1 VARCHAR /*** MSC-WARNING - MSCEWI1036 - VARCHAR2 DATA TYPE CONVERTED TO VARCHAR ***/)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        DECLARE
            statement_variable VARCHAR(500) /*** MSC-WARNING - MSCEWI1036 - VARCHAR2 DATA TYPE CONVERTED TO VARCHAR ***/ := '/*** MSC-WARNING - MSCEWI3084 - PURGE REMOVED FROM DROP TABLE STATEMENT. ***/
DROP TABLE ' || NVL(:param1 :: STRING, '');
        BEGIN
            --** MSC-ERROR - MSCCP0004 - 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

Query

CREATE TABLE immediate_inserted_table(COL1 INTEGER);

CREATE OR REPLACE PROCEDURE inserting_procedure_using(param1 INTEGER)
AS
BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO immediate_inserted_table VALUES (:1)' USING param1;
END;

CALL inserting_procedure_using(1);

SELECT * FROM immediate_inserted_table;

Results

Snowflake Scripting

Please note parenthesis are required for parameters in the USING Clause in Snowflake Scripting.

Query

CREATE OR REPLACE TABLE immediate_inserted_table (COL1 INTEGER);

CREATE OR REPLACE PROCEDURE inserting_procedure_using (param1 INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
        EXECUTE IMMEDIATE 'INSERT INTO immediate_inserted_table
VALUES (?)' USING ( param1);
    END;
$$;

CALL inserting_procedure_using(1);

SELECT * FROM
    immediate_inserted_table;

Results

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

Query

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.

Query

CREATE OR REPLACE PROCEDURE inserting_procedure_variable_execute_concatenation_parameter (param1 INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        query VARCHAR(500) /*** MSC-WARNING - MSCEWI1036 - VARCHAR2 DATA TYPE CONVERTED TO VARCHAR ***/ := 'INSERT INTO immediate_inserted_table VALUES (';
    BEGIN
-- ** MSC-ERROR - MSCEWI1027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED **
--        --** MSC-ERROR - MSCCP0004 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. **
--        EXECUTE IMMEDIATE NVL(:query :: STRING, '') || NVL(:param1 :: STRING, '') || ')'
                                                                                        ;
    END;
$$;

Related EWIs

1. MSCEWI1027: Variable with invalid query.

2. MSCEWI1058: Functionality is not currently supported by Snowflake Scripting.

3. MSCEWI3109: Expressions as arguments of Using Clause are not supported by Snowflake Scripting.

4. MSCCP0004: The statement below has usages of dynamic SQL.