LOOP

Translation reference to convert Oracle LOOP statement to Snowflake Scripting

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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.