SSC-EWI-OR0109

Expressions as arguments of Using Clause are not supported by Snowflake Scripting

Severity

Medium

Description

Oracle supports using expressions as arguments to any USING Clause for the EXECUTE IMMEDIATE statements. This functionality is not supported by Snowflake Scripting.

Snowflake Scripting does support variable expressions, and this it is possible to replace the expression by manually assigning it to a variable (see example below).

Example Code

Input Code:

IN -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE expression_arguments
IS
  immediate_input INTEGER := 0;
BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO immediate_table VALUES (:value)' USING immediate_input+1;
END;

Output Code:

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE expression_arguments ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    immediate_input INTEGER := 0;
  BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
    EXECUTE IMMEDIATE 'INSERT INTO immediate_table
VALUES (?)' USING (
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0109 - EXPRESSIONS AS ARGUMENTS OF USING CLAUSE IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
    :immediate_input +1);
  END;
$$;

Manually migrated Execute Immediate procedure:

Replacing this procedure with the one above will solve the compilation error, and yield the same results as Oracle.

CREATE OR REPLACE PROCEDURE PUBLIC.expression_arguments ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      immediate_input INTEGER := 0;
      using_argument_variable INTEGER;
   BEGIN
      using_argument_variable := immediate_input+1;
      EXECUTE IMMEDIATE 'INSERT INTO PUBLIC.immediate_table VALUES (?)' USING (using_argument_variable );
   END;
$$;

Recommendations

• Procedures can be manually migrated by adding a variable and then assigning the expression to said variable.

• If you need more support, you can email us at [e-mail]