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

IN -> Oracle_01.sql

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

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 VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    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

Query

IN -> Oracle_02.sql

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

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 VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    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