SSC-EWI-OR0101

Specific For Loop Clause Is Currently Not Supported By Snowflake Scripting

Severity

Low

Description

Oracle allows additional clauses to the FOR LOOP condition. Like the BY, WHILE, and WHEN clauses. Both WHILE and WHEN clauses allow for an extra boolean expression as a condition. While the BY clause allows a stepped increment in the iteration. These additional clauses are not supported in Snowflake Scripting and are ignored during transformation.

Example Code

Input Code Oracle:

IN -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE P2
AS
BEGIN
FOR i IN 1..10 WHILE i <= 5 LOOP
    NULL;
END LOOP;

FOR i IN 5..15 BY 5 LOOP
    NULL;
END LOOP;
END;

Output Code:

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE P2 ()
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-OR0101 - FOR LOOP WITH "WHILE" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        FOR i IN 1 TO 10 LOOP
            NULL;
        END LOOP;
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0101 - FOR LOOP WITH "BY" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        FOR i IN 5 TO 15 LOOP
            NULL;
        END LOOP;
    END;
$$;

Recommendations

• Separate the FOR LOOP into different loops or rewrite the condition.

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