PARAMETRIZED CURSOR

Parametrized Cursor is not supported by Snowflake Scripting

Some parts in the output code are omitted for clarity reasons.

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 Oracle:

IN -> Oracle_01.sql

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:

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE parametrized_cursor_sample ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        cursor1 CURSOR
        FOR
            SELECT
                OBJECT_CONSTRUCT('REGION_NAME', region_name) sc_cursor_record FROM
                hr.regions
            where region_id = param1 ORDER BY region_name;
                var1 integer;
    BEGIN
                OPEN cursor1;
                FETCH cursor1 INTO
            :var1;
    CLOSE cursor1;
                OPEN cursor1;
                --** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
                FOR r1 IN cursor1 DO
            LET r1 OBJECT := r1.sc_cursor_record;
                   NULL;
                END FOR;
                CLOSE cursor1;
    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]

Related EWIs

1. SSC-PRF-0004: This statement has usages of cursor for loop.