SSC-EWI-OR0103

For Loop Format Is Currently Not Supported By Snowflake Scripting

Severity

High

Description

Oracle allows different types of conditions for a FOR LOOP. It supports boolean expressions, collections, records... However, Snowflake scripting only supports FOR LOOP with defined integers as bounds. All other formats are marked as not supported and require additional manual effort to be transformed.

Oracle iteration control clauses that are not supported in Snowflake FOR LOOP:

• single_expression_control

• values_of_control

• indices_of_control

• pairs_of_control

cursor_iteration_control is currently marked as not supported. Removing parenthesis from the expression should transform it as a CURSOR FOR LOOP.

Original:

FOR i IN (cursor_variable) LOOP NULL; END LOOP;

Should be changed to:

FOR i IN cursor_variable LOOP NULL; END LOOP;

Example Code

Input Code Oracle:

IN -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE P3
AS
TYPE values_aat IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
l_employee_values   values_aat;
BEGIN
FOR power IN REPEAT power*2 WHILE power <= 64 LOOP
    NULL;
END LOOP;

FOR i IN VALUES OF l_employee_values LOOP
    NULL;
END LOOP; 
END;

Output Code:

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE P3 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
--        !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
--        TYPE values_aat IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
        l_employee_values VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'values_aat' USAGE CHANGED TO VARIANT ***/!!!;
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0103 - FOR LOOP FORMAT IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-OR0101 - FOR LOOP WITH "WHILE" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
        FOR power IN REPEAT power*2 WHILE power <= 64 LOOP
            NULL;
        END LOOP;
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0103 - FOR LOOP FORMAT IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

        FOR i IN VALUES OF :l_employee_values LOOP
            NULL;
        END LOOP;
    END;
$$;

Recommendations

• Rewrite the FOR LOOP condition or use a different kind of LOOP to simulate the behavior.

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