FOR LOOP

Description

With each iteration of the FOR LOOP statement, its statements run, its index is either incremented or decremented, and control returns to the top of the loop. (Oracle PL/SQL Language Reference FOR LOOP Statement).

Oracle Syntax

FOR
pls_identifier [ MUTABLE | IMMUTABLE ] [ constrained_type ]
[ , iterand_decl ]

IN

[ REVERSE ] iteration_control pred_clause_seq
[, qual_iteration_ctl]...

LOOP
statement... 
END LOOP [ label ] ;

Snowflake Scripting Syntax

FOR <counter_variable> IN [ REVERSE ] <start> TO <end> { DO | LOOP }
    statement;
    [ statement; ... ]
END { FOR | LOOP } [ <label> ] ;

Snowflake Scripting supports FOR LOOP that loops a specified number of times. The upper and lower bounds must be INTEGER. Check more information in the Snowflake Scripting documentation.

Oracle FOR LOOP behavior can also be modified by using the statements:

• CONTINUE

• EXIT

• GOTO

• RAISE

Sample Source Patterns

1. FOR LOOP

This case is functionally equivalent.

Oracle FOR LOOP Example

IN -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE P1
AS
BEGIN
    FOR i IN 1..10
    LOOP
        NULL;
    END LOOP;

    FOR i IN VAR1..VAR2
    LOOP
        NULL;
    END LOOP; 

    FOR i IN REVERSE 1+2..10+5
    LOOP
        NULL;
    END LOOP; 
END;

Snowflake Scripting FOR LOOP Example

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE P1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        FOR i IN 1 TO 10 LOOP
            NULL;
        END LOOP;
        FOR i IN VAR1 TO VAR2 LOOP
            NULL;
        END LOOP;
        FOR i IN REVERSE 1+2 TO 10+5 LOOP
            NULL;
        END LOOP;
    END;
$$;

2. FOR LOOP with additional clauses

Oracle FOR LOOP Example

IN -> Oracle_02.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;

Snowflake Scripting FOR LOOP Example

OUT -> Oracle_02.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;
$$;

3. FOR LOOP with multiple conditions

Oracle FOR LOOP Example

IN -> Oracle_03.sql

CREATE OR REPLACE PROCEDURE P3
AS
BEGIN
    FOR i IN REVERSE 1..3,
    REVERSE i+5..i+7
    LOOP
        NULL;
    END LOOP; 
END;

Snowflake Scripting FOR LOOP Example

OUT -> Oracle_03.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
$$
    BEGIN
        --** SSC-FDM-OR0022 - FOR LOOP WITH MULTIPLE CONDITIONS IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
        FOR i IN REVERSE 1 TO 3 LOOP
            NULL;
        END LOOP;
    END;
$$;

4. FOR LOOP with unsupported format

Oracle FOR LOOP Example

IN -> Oracle_04.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;

Snowflake Scripting FOR LOOP Example

OUT -> Oracle_04.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
--        --** SSC-FDM-0024 - 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;
$$;

Transformation for custom types is currently not supported for Snowflake Scripting.

Known Issues

1. For With Multiple Conditions

Oracle allows multiple conditions in a single FOR LOOP however, Snowflake Scripting only allows one condition per FOR LOOP. Only the first condition is migrated and the others are ignored during transformation. Check SSC-FDM-OR0022.

Oracle

FOR i IN REVERSE 1..3,
REVERSE i+5..i+7
LOOP
    NULL;
END LOOP;

Snowflake Scripting FOR LOOP Example

--** SSC-FDM-OR0022 - FOR LOOP WITH MULTIPLE CONDITIONS IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
FOR i IN REVERSE 1 TO 3 LOOP
    NULL;
END LOOP;

2. Mutable vs Inmutable Counter Variable

Oracle allows modifying the value of the FOR LOOP variable inside the loop. The current documentation includes this functionality but Snowflake recommends avoiding this. Modifying the value of this variable may not behave correctly in Snowflake Scripting.

3. Integer vs Float number for Upper or Lower Bound

Snowflake Scripting only allows an INTEGER or an expression that evaluates to an INTEGER as a bound for the FOR LOOP condition. Floating numbers will be rounded up or down and alter the original bound. Check SSC-EWI-OR0102.

4. Oracle Unsupported Clauses

Oracle allows additional clauses to the FOR LOOP condition. Like the BY clause for a stepped increment in the condition. And the WHILE and WHEN clause for boolean expressions. These additional clauses are not supported in Snowflake Scripting and are ignored during transformation. Check SSC-EWI-OR0101.

Oracle

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

Snowflake Scripting

!!!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;

5. Unsupported Formats

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. Check SSC-EWI-OR0103.

Related EWIs

1. SSC-FDM-0024: Functionality is not currently supported by Snowflake Scripting.

2. SSC-EWI-0062: Custom type usage changed to variant.

3. SSC-FDM-OR0022: For Loop With Multiple Conditions Is Currently Not Supported By Snowflake Scripting. Only First Condition Is Used.

4. SSC-EWI-OR0101: Specific For Loop Clause Is Currently Not Supported By Snowflake Scripting.

5. SSC-EWI-OR0102: For Loop With Float Number As Bound May Not Behave Correctly In Snowflake Scripting.

6. SSC-EWI-OR0103: For Loop Format Is Currently Not Supported By Snowflake Scripting.