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).

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

IN

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

LOOP
statement... 
END LOOP [ label ] ;
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:

Sample Source Patterns

1. FOR LOOP

This case is functionally equivalent.

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;
CREATE OR REPLACE PROCEDURE PUBLIC.P1 ()
RETURNS VARCHAR
LANGUAGE SQL
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

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;
CREATE OR REPLACE PROCEDURE PUBLIC.P2 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      /*** MSC-WARNING - MSCEWI3101 - FOR LOOP WITH "WHILE" CLAUSE IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/
      FOR i IN 1 TO 10 LOOP
         NULL;
     END LOOP;
      /*** MSC-WARNING - MSCEWI3101 - 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

CREATE OR REPLACE PROCEDURE P3
AS
BEGIN
FOR i IN REVERSE 1..3,
REVERSE i+5..i+7
LOOP
    NULL;
END LOOP; 
END;
CREATE OR REPLACE PROCEDURE PUBLIC.P3 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      /*** MSC-WARNING - MSCEWI3100 - 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

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;
CREATE OR REPLACE PROCEDURE PUBLIC.P3 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      l_employee_values VARIANT /*** MSC-WARNING - MSCEWI1055 - REFERENCED CUSTOM TYPE 'values_aat' NOT FOUND ***/;
   BEGIN
-- ** MSC-ERROR - MSCEWI3103 - FOR LOOP FORMAT IS CURRENTLY NOT SUPPORTED BY SNOWFLAKE SCRIPTING **
--      /*** MSC-WARNING - MSCEWI3101 - 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
              ;

-- ** MSC-ERROR - MSCEWI3103 - 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 MSCEWI3100.

FOR i IN REVERSE 1..3,
REVERSE i+5..i+7
LOOP
    NULL;
END LOOP;
/*** MSC-WARNING - MSCEWI3100 - 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 MSCEWI3102.

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 MSCEWI3101.

FOR i IN 5..15 BY 5 LOOP
    NULL;
END LOOP;
/*** MSC-WARNING - MSCEWI3101 - 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 MSCEWI3103.

Related EWIs

  1. MSCEWI3100: For Loop With Multiple Conditions Is Currently Not Supported By Snowflake Scripting.

  2. MSCEWI3101: Specific For Loop Clause Is Currently Not Supported By Snowflake Scripting.

  3. MSCEWI3102: For Loop With Float Number As Bound May Not Behave Correctly In Snowflake Scripting.

  4. MSCEWI3103: For Loop Format Is Currently Not Supported By Snowflake Scripting.

PreviousFORALLNextHELPERS

Last updated