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)

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.

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

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

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
        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_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

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
            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)
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

OUT -> Oracle_03.sql
CREATE OR REPLACE TABLE immediate_dropped_table (
    col1 INTEGER