MSCEWI3106

Parametrized Cursor is not supported by Snowflake Scripting

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

High

Description

Oracle supports parameters for cursors that are declared. However, Snowflake Scripting does not support this feature, so the declaration and the usage of the cursor are not possible.

Example Code

Input Code:

CREATE OR REPLACE PROCEDURE parametrized_cursor_sample AS
    CURSOR cursor1(param1 number) IS SELECT region_name FROM hr.regions where region_id = param1 ORDER BY region_name;
    var1 integer;
BEGIN
    OPEN cursor1(123);
    FETCH cursor1 INTO var1;
    CLOSE cursor1;
    FOR r1 IN cursor1(456) LOOP
        NULL;
    END LOOP;
END;

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.parametrized_cursor_sample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      var1 integer;
-- ** MSC-ERROR - MSCEWI3106 - PARAMETRIZED CURSOR IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
--      CURSOR cursor1(param1 FLOAT) IS SELECT region_name FROM hr.regions where region_id = param1 ORDER BY region_name
                                                                                                                      ;
   BEGIN
      NULL;
-- ** MSC-ERROR - MSCEWI3106 - PARAMETRIZED CURSOR IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
--       OPEN cursor1(123)
                        ;
-- ** MSC-ERROR - MSCEWI3106 - PARAMETRIZED CURSOR IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
--       FETCH cursor1 INTO var1
                              ;
-- ** MSC-ERROR - MSCEWI3106 - PARAMETRIZED CURSOR IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
--       CLOSE cursor1
                    ;
-- ** MSC-ERROR - MSCEWI3106 - PARAMETRIZED CURSOR IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
--       FOR r1 IN cursor1(456) LOOP
--           NULL;
--       END LOOP
               ;
   END;
$$;

Recommendations

• Try using bindings for the query in the cursor and open the cursor with the USING clause. Keep in mind that a parameter that is used multiple times on a single cursor may require passing the variable multiple times in the USING clause.

Snowflake Query

CREATE OR REPLACE PROCEDURE PUBLIC.parametrized_cursor_sample_fixed ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      var1 STRING;
      cursor1 CURSOR FOR SELECT region_name FROM hr.regions where region_id = ? ORDER BY region_name;
   BEGIN
      NULL;
      OPEN cursor1 USING (1);
      FETCH cursor1 INTO var1;
      CLOSE cursor1;
      OPEN cursor1 USING (2);
      FOR r1 IN cursor1 DO
         NULL;
      END FOR;
      CLOSE cursor1;
   END;
$$;

• Manually change the cursor to use bindings.

• If you need more support, you can email us at [email protected]