WHILE

Translation reference to convert Oracle WHILE statement to Snowflake Scripting

Description

The WHILE LOOP statement runs one or more statements while a condition is TRUE. (Oracle PL/SQL Language Reference WHILE Statement)

Oracle WHILE Syntax

WHILE boolean_expression
  LOOP statement... END LOOP [ label ] ;

Snowflake Scripting WHILE Syntax

WHILE ( <condition> ) { DO | LOOP }
  <statement>;
  [ <statement>; ... ]
END { WHILE | LOOP } [ <label> ] ;

Oracle WHILE behavior can also be modified by using the statements:

• CONTINUE

• EXIT

• GOTO

• RAISE

Sample Source Patterns

While simple case

This case is functionally equivalent.

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE while_testing_table
(
    iterator VARCHAR2(5)
);

CREATE OR REPLACE PROCEDURE while_procedure 
IS
I NUMBER := 1;
J NUMBER := 10;
BEGIN  
  WHILE I <> J LOOP
    INSERT INTO while_testing_table VALUES(TO_CHAR(I));
    I := I+1;    
  END LOOP;
END;

CALL while_procedure();
SELECT * FROM while_testing_table;

Result

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

Snowflake Scripting

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE while_testing_table
(
    iterator VARCHAR(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE while_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
    I NUMBER(38, 18) := 1;
    J NUMBER(38, 18) := 10;
BEGIN
    WHILE (:I <> :J) LOOP
      INSERT INTO while_testing_table
      VALUES(TO_CHAR(:I));
      I := :I +1;
    END LOOP;
END;
$$;

CALL while_procedure();

SELECT * FROM
while_testing_table;

Result

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

Known Issues

No issues were found.

Related EWIs

No related EWIs.