LOOP

Translation reference to convert Oracle LOOP statement to Snowflake Scripting

Description

With each iteration of the basic LOOP statement, its statements run and control returns to the top of the loop. The LOOP statement ends when a statement inside the loop transfers control outside the loop or raises an exception. (Oracle PL/SQL Language Reference BASIC LOOP Statement)

Oracle BASIC LOOP Syntax

LOOP statement... END LOOP [ label ] ;

Snowflake Scripting BASIC LOOP Syntax

LOOP
  <statement>;
  [ <statement>; ... ]
END LOOP [ <label> ] ;

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

• CONTINUE

• EXIT

• GOTO

• RAISE

Sample Source Patterns

Loop simple case

This case is functionally equivalent.

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE loop_testing_table
(
    iterator VARCHAR2(5)
);

CREATE OR REPLACE PROCEDURE loop_procedure 
IS
I NUMBER := 1;
J NUMBER := 10;
BEGIN  
  LOOP
    EXIT WHEN I = J;
    INSERT INTO loop_testing_table VALUES(TO_CHAR(I));
    I := I+1;
  END LOOP;
END;

CALL loop_procedure();
SELECT * FROM loop_testing_table;

Result

ITERATOR|
--------+
1       |
2       |
3       |
4       |
5       |
6       |
7       |
8       |
9       |

Snowflake Scripting

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE loop_testing_table
  (
      iterator VARCHAR(5)
  )
  COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
  ;

  CREATE OR REPLACE PROCEDURE loop_procedure ()
  RETURNS VARCHAR
  LANGUAGE SQL
  COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "07/14/2025",  "domain": "no-domain-provided" }}'
  EXECUTE AS CALLER
  AS
  $$
  DECLARE
      I NUMBER(38, 18) := 1;
      J NUMBER(38, 18) := 10;
  BEGIN
      --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
      LOOP
        IF (:I = :J) THEN
          EXIT;
        END IF;
        INSERT INTO loop_testing_table
        VALUES(TO_CHAR(:I));
        I := :I +1;
      END LOOP;
  END;
  $$;

  CALL loop_procedure();

  SELECT * FROM
  loop_testing_table;

Result

ITERATOR|
--------+
1       |
2       |
3       |
4       |
5       |
6       |
7       |
8       |
9       |

Known Issues

No issues were found.

Related EWIs

No related EWIs.