SnowConvert:PL/SQL to Snowflake Scripting¶

 CREATE TABLE TASSIGN (
    COL1 NUMBER,
    COL2 NUMBER,
    COL3 VARCHAR(20),
    COL4 VARCHAR(20)
);

CREATE OR REPLACE PROCEDURE PSCALAR
AS
   var1  NUMBER := 40;
   var2  NUMBER := 22.50;
   var3  VARCHAR(20);
   var4  BOOLEAN;
   var5  NUMBER;
BEGIN
   var1 := 1;
   var2 := 2.1;
   var2 := var2 + var2;
   var3 := 'Hello World';
   var4 := true;
   var4 := var1 > 500;
   IF var4 THEN
      var5 := 0;
   ELSE
      var5 := 1;
   END IF;
  INSERT INTO TASSIGN VALUES(var1, var2, var3, var5);
END;

CALL PSCALAR();

SELECT * FROM TASSIGN;

 CREATE OR REPLACE TABLE TASSIGN (
     COL1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
     COL2 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
     COL3 VARCHAR(20),
     COL4 VARCHAR(20)
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 ;

 CREATE OR REPLACE PROCEDURE PSCALAR ()
 RETURNS VARCHAR
 LANGUAGE SQL
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
 EXECUTE AS CALLER
 AS
 $$
     DECLARE
     var1 NUMBER(38, 18) := 40;
     var2 NUMBER(38, 18) := 22.50;
     var3  VARCHAR(20);
     var4  BOOLEAN;
     var5 NUMBER(38, 18);
     BEGIN
     var1 := 1;
     var2 := 2.1;
     var2 := :var2 + :var2;
     var3 := 'Hello World';
     var4 := true;
     var4 := :var1 > 500;
     IF (:var4) THEN
       var5 := 0;
       ELSE
       var5 := 1;
       END IF;
       INSERT INTO TASSIGN
       VALUES(:var1, :var2, :var3, :var5);
     END;
 $$;

 CALL PSCALAR();
 
SELECT * FROM
     TASSIGN;

 CREATE OR REPLACE PROCEDURE pinvalid(out_parameter   IN OUT NUMBER)
AS
record_variable       employees%ROWTYPE;

TYPE cursor_type IS REF CURSOR;
cursor1   cursor_type;
cursor2   SYS_REFCURSOR;

TYPE collection_type IS TABLE OF NUMBER INDEX BY VARCHAR(64);
collection_variable     collection_type;

BEGIN
--Record Example
  record_variable.last_name := 'Ortiz';

--Cursor Example
  cursor1 := cursor2;
  
--Collection
  collection_variable('Test') := 5;

--Out Parameter
  out_parameter := 123;
END;

 --** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "employees" **
CREATE OR REPLACE PROCEDURE pinvalid (out_parameter NUMBER(38, 18))
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    record_variable OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
--    !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL REF CURSOR TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

--    TYPE cursor_type IS REF CURSOR;
    cursor1_res RESULTSET;
    cursor2_res RESULTSET;
--    !!!RESOLVE EWI!!! /*** SSC-EWI-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!

--    TYPE collection_type IS TABLE OF NUMBER INDEX BY VARCHAR(64);
    collection_variable VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'collection_type' USAGE CHANGED TO VARIANT ***/!!!;
  BEGIN
    --Record Example
    record_variable := OBJECT_INSERT(record_variable, 'LAST_NAME', 'Ortiz', true);

    --Cursor Example
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
      cursor1 := :cursor2;

    --Collection
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0108 - THE FOLLOWING ASSIGNMENT STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
      collection_variable('Test') := 5;
    --Out Parameter
    out_parameter := 123;
    RETURN out_parameter;
  END;
$$;

 CREATE OR REPLACE PROCEDURE caseExample1 ( grade NUMBER )
IS
RESULT VARCHAR(20);
BEGIN
   <<CASE1>>
   CASE grade
    WHEN 10 THEN RESULT:='Excellent';
    WHEN 9 THEN RESULT:='Very Good';
    WHEN 8 THEN RESULT:='Good';
    WHEN 7 THEN RESULT:='Fair';
    WHEN 6 THEN RESULT:='Poor';
    ELSE RESULT:='No such grade';
  END CASE CASE1;
  INSERT INTO CASE_TABLE(COL) VALUES (RESULT);
END;

CALL caseExample1(6);

CALL caseExample1(4);

CALL caseExample1(10);

SELECT * FROM CASE_TABLE;

 CREATE OR REPLACE PROCEDURE caseExample1 (grade NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    RESULT VARCHAR(20);
  BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<CASE1>> ***/!!!
    CASE :grade
      WHEN 10 THEN
        RESULT := 'Excellent';
      WHEN 9 THEN
        RESULT := 'Very Good';
      WHEN 8 THEN
        RESULT := 'Good';
      WHEN 7 THEN
        RESULT := 'Fair';
      WHEN 6 THEN
        RESULT := 'Poor';
        ELSE
        RESULT := 'No such grade';
    END CASE;
    INSERT INTO CASE_TABLE(COL) VALUES (:RESULT);
  END;
$$;

CALL caseExample1(6);

CALL caseExample1(4);

CALL caseExample1(10);

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "CASE_TABLE" **

SELECT * FROM
  CASE_TABLE;

 CREATE OR REPLACE PROCEDURE caseExample2 ( grade NUMBER )
IS
RESULT VARCHAR(20);
BEGIN
    <<CASE1>>
    CASE
    	WHEN grade = 10 THEN RESULT:='Excellent';
    	WHEN grade = 9 THEN RESULT:='Very Good';
    	WHEN grade = 8 THEN RESULT:='Good';
    	WHEN grade = 7 THEN RESULT:='Fair';
    	WHEN grade = 6 THEN RESULT:='Poor';
    	ELSE RESULT:='No such grade';
  END CASE CASE1;
  INSERT INTO CASE_TABLE(COL) VALUES (RESULT);
END;

CALL caseExample2(6);
CALL caseExample2(4);
CALL caseExample2(10);
SELECT * FROM CASE_TABLE;

 CREATE OR REPLACE PROCEDURE caseExample2 (grade NUMBER(38, 18))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    RESULT VARCHAR(20);
  BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<CASE1>> ***/!!!
    CASE
      WHEN :grade = 10 THEN
        RESULT := 'Excellent';
      WHEN :grade = 9 THEN
        RESULT := 'Very Good';
      WHEN :grade = 8 THEN
        RESULT := 'Good';
      WHEN :grade = 7 THEN
        RESULT := 'Fair';
      WHEN :grade = 6 THEN
        RESULT := 'Poor';
        ELSE
        RESULT := 'No such grade';
    END CASE;
    INSERT INTO CASE_TABLE(COL) VALUES (:RESULT);
  END;
$$;

CALL caseExample2(6);

CALL caseExample2(4);

CALL caseExample2(10);

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "CASE_TABLE" **
SELECT * FROM
  CASE_TABLE;

 DECLARE
    age NUMBER := 18;
BEGIN
    IF age >= 18 THEN 
        DBMS_OUTPUT.PUT_LINE('You are an adult.');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('You are a minor.');
    END IF;
END;

 DECLARE
    age NUMBER(38, 18) := 18;
    call_results VARIANT;
BEGIN
    IF (:age >= 18) THEN
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        call_results := (
            CALL DBMS_OUTPUT.PUT_LINE_UDF('You are an adult.')
        );
    ELSE
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        call_results := (
            CALL DBMS_OUTPUT.PUT_LINE_UDF('You are a minor.')
        );
    END IF;
    RETURN call_results;
END;

 anonymous block
You are an adult.

 BEGIN
   DECLARE
      day_of_week NUMBER := 3;
   BEGIN
      CASE day_of_week
         WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('Sunday');
         WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('Monday');
         WHEN 3 THEN DBMS_OUTPUT.PUT_LINE('Tuesday');
         WHEN 4 THEN DBMS_OUTPUT.PUT_LINE('Wednesday');
         WHEN 5 THEN DBMS_OUTPUT.PUT_LINE('Thursday');
         WHEN 6 THEN DBMS_OUTPUT.PUT_LINE('Friday');
         WHEN 7 THEN DBMS_OUTPUT.PUT_LINE('Saturday');
         ELSE DBMS_OUTPUT.PUT_LINE('Invalid day');
      END CASE;
   END;
END;

 DECLARE
   call_results VARIANT;
BEGIN
   DECLARE
      day_of_week NUMBER(38, 18) := 3;
   BEGIN
      CASE :day_of_week
         WHEN 1 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Sunday')
            );
         WHEN 2 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Monday')
            );
         WHEN 3 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Tuesday')
            );
         WHEN 4 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Wednesday')
            );
         WHEN 5 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Thursday')
            );
         WHEN 6 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Friday')
            );
         WHEN 7 THEN
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Saturday')
            );
         ELSE
            --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
            call_results := (
               CALL DBMS_OUTPUT.PUT_LINE_UDF('Invalid day')
            );
      END CASE;
   END;
   RETURN call_results;
END;

 anonymous block
Tuesday

 BEGIN
    FOR i IN 1..10 LOOP
        NULL;
    END LOOP;
END;

 BEGIN
    FOR i IN 1 TO 10 LOOP
        NULL;
    END LOOP;
END;

 -- Procedure declaration
CREATE OR REPLACE PROCEDURE calculate_sum(
    p_num1 IN NUMBER,
    p_num2 IN NUMBER,
    p_result OUT NUMBER
)
IS
BEGIN
    -- Calculate the sum of the two numbers
    p_result := p_num1 + p_num2;
END;
/

-- Anonymous block with a procedure call
DECLARE
    -- Declare variables to hold the input and output values
    v_num1 NUMBER := 10;
    v_num2 NUMBER := 20;
    v_result NUMBER;
BEGIN
    -- Call the procedure with the input values and get the result
    calculate_sum(v_num1, v_num2, v_result);
    
    -- Display the result
    DBMS_OUTPUT.PUT_LINE('The sum of ' || v_num1 || ' and ' || v_num2 || ' is ' || v_result);
END;
/

 -- Procedure declaration
CREATE OR REPLACE PROCEDURE calculate_sum (p_num1 NUMBER(38, 18), p_num2 NUMBER(38, 18), p_result NUMBER(38, 18)
)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
    -- Calculate the sum of the two numbers
        p_result := :p_num1 + :p_num2;
        RETURN p_result;
    END;
$$;

-- Anonymous block with a procedure call
DECLARE
    -- Declare variables to hold the input and output values
    v_num1 NUMBER(38, 18) := 10;
    v_num2 NUMBER(38, 18) := 20;
    v_result NUMBER(38, 18);
    call_results VARIANT;
BEGIN
    call_results := (
        CALL
        -- Call the procedure with the input values and get the result
        calculate_sum(:v_num1, :v_num2, :v_result)
    );
    v_result := :call_results;

    -- Display the result
    --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
    call_results := (
        CALL DBMS_OUTPUT.PUT_LINE_UDF('The sum of ' || NVL(:v_num1 :: STRING, '') || ' and ' || NVL(:v_num2 :: STRING, '') || ' is ' || NVL(:v_result :: STRING, ''))
    );
    RETURN call_results;
END;

 DECLARE
     lv_sql_txt VARCHAR2(200);
BEGIN
     lv_sql_txt := 'ALTER SESSION SET nls_date_format = ''DD-MM-YYYY''';
     EXECUTE IMMEDIATE lv_sql_txt;
END;

 DECLARE
     lv_sql_txt VARCHAR(200);
BEGIN
     lv_sql_txt := 'ALTER SESSION SET nls_date_format = ''DD-MM-YYYY''';
     !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!! 
     EXECUTE IMMEDIATE :lv_sql_txt;
END;

 CREATE TABLE employee (
    ID_Number	NUMBER,
    emp_Name	VARCHAR(200),
    emp_Phone	NUMBER
);

INSERT INTO employee VALUES (1, 'NameA NameZ', 1234567890);
INSERT INTO employee VALUES (2, 'NameB NameY', 1234567890);

DECLARE
    var1 VARCHAR(20);
    CURSOR cursor1 IS SELECT emp_Name FROM employee ORDER BY ID_Number;
BEGIN
    OPEN cursor1;
    FETCH cursor1 INTO var1;
    CLOSE cursor1;
	DBMS_OUTPUT.PUT_LINE(var1);
END;

 CREATE OR REPLACE TABLE employee (
	   ID_Number NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
	   emp_Name	VARCHAR(200),
	   emp_Phone NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

INSERT INTO employee
VALUES (1, 'NameA NameZ', 1234567890);

INSERT INTO employee
VALUES (2, 'NameB NameY', 1234567890);

DECLARE
    var1 VARCHAR(20);
	   cursor1 CURSOR
	   FOR
		SELECT emp_Name FROM
			employee
		ORDER BY ID_Number;
	   call_results VARIANT;
BEGIN
	   OPEN cursor1;
	   FETCH cursor1 INTO
		:var1;
	   CLOSE cursor1;
	   --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
	   call_results := (
		CALL DBMS_OUTPUT.PUT_LINE_UDF(:var1)
	   );
	   RETURN call_results;
END;

 CREATE TABLE employee (
    ID_Number NUMBER,
    emp_Name VARCHAR(200),
    emp_Phone NUMBER
);

INSERT INTO employee VALUES (1, 'NameA NameZ', 1234567890);
INSERT INTO employee VALUES (2, 'NameB NameY', 1234567890);

DECLARE
    var_Result NUMBER;
BEGIN
    SELECT COUNT(*) INTO var_Result FROM employee;
    DBMS_OUTPUT.PUT_LINE(var_Result);
END;

 CREATE OR REPLACE TABLE employee (
       ID_Number NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
       emp_Name VARCHAR(200),
       emp_Phone NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
   ;

   INSERT INTO employee
   VALUES (1, 'NameA NameZ', 1234567890);

   INSERT INTO employee
   VALUES (2, 'NameB NameY', 1234567890);

   DECLARE
    var_Result NUMBER(38, 18);
       call_results VARIANT;
   BEGIN
       SELECT COUNT(*) INTO
           :var_Result
       FROM
           employee;
       --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
       call_results := (
           CALL DBMS_OUTPUT.PUT_LINE_UDF(:var_Result)
       );
       RETURN call_results;
   END;

 CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);

INSERT INTO t1 (col1) VALUES (2);
INSERT INTO t1 (col1) VALUES (3);
INSERT INTO t1 (col1) VALUES (4);

INSERT INTO t2 (col1) VALUES (1);
INSERT INTO t2 (col1) VALUES (2);
INSERT INTO t2 (col1) VALUES (2);
INSERT INTO t2 (col1) VALUES (3);


DECLARE
    total_price FLOAT;
    CURSOR cursor1 IS SELECT t1.col1 as FirstTable, t2.col1 as SecondTable
    FROM t1 INNER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1,2;
BEGIN
    total_price := 0.0;
    FOR rec IN cursor1 LOOP
      total_price := total_price + rec.FirstTable;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(total_price);
END;

 CREATE OR REPLACE TABLE t1 (col1 INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE TABLE t2 (col1 INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

INSERT INTO t1(col1) VALUES (2);

INSERT INTO t1(col1) VALUES (3);

INSERT INTO t1(col1) VALUES (4);

INSERT INTO t2(col1) VALUES (1);

INSERT INTO t2(col1) VALUES (2);

INSERT INTO t2(col1) VALUES (2);

INSERT INTO t2(col1) VALUES (3);

DECLARE
    total_price FLOAT;
    cursor1 CURSOR
    FOR
        SELECT t1.col1 as FIRSTTABLE, t2.col1 as SECONDTABLE
           FROM
            t1
            INNER JOIN
                t2
               ON t2.col1 = t1.col1
           ORDER BY 1,2;
    call_results VARIANT;
BEGIN
    total_price := 0.0;
    OPEN cursor1;
    --** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
    FOR rec IN cursor1 DO
        total_price :=
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN AproxNumeric AND unknown ***/!!!
        :total_price + rec.FIRSTTABLE;
    END FOR;
    CLOSE cursor1;
    --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
    call_results := (
        CALL DBMS_OUTPUT.PUT_LINE_UDF(:total_price)
    );
    RETURN call_results;
END;

 DECLARE
      v_result NUMBER;
BEGIN
   v_result := 1 / 0;
   EXCEPTION
      WHEN ZERO_DIVIDE THEN
         DBMS_OUTPUT.PUT_LINE( SQLERRM );
END;

 DECLARE
      v_result NUMBER(38, 18);
      error_results VARIANT;
BEGIN
      v_result := 1 / 0;
   EXCEPTION
      WHEN ZERO_DIVIDE THEN
      --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
      error_results := (
         CALL DBMS_OUTPUT.PUT_LINE_UDF( SQLERRM )
      );
      RETURN error_results;
END;

 CREATE TABLE continue_testing_table_2 (iterator VARCHAR2(5));

CREATE OR REPLACE PROCEDURE continue_procedure_2
IS
I NUMBER := 0;
J NUMBER := 20;
BEGIN
    WHILE I <= J LOOP
        I := I + 1;
        CONTINUE WHEN MOD(I,2) = 0;
        INSERT INTO continue_testing_table_2 VALUES(TO_CHAR(I));
    END LOOP;  
END;

CALL continue_procedure_2();
SELECT * FROM continue_testing_table_2;

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

CREATE OR REPLACE PROCEDURE continue_procedure_2 ()
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) := 0;
        J NUMBER(38, 18) := 20;
    BEGIN
        WHILE (:I <= :J) LOOP
            I := :I + 1;
            IF (MOD(:I,2) = 0) THEN
                CONTINUE;
            END IF;
            INSERT INTO continue_testing_table_2
            VALUES(TO_CHAR(:I));
        END LOOP;
    END;
$$;

CALL continue_procedure_2();

SELECT * FROM
    continue_testing_table_2;

 CREATE OR REPLACE PROCEDURE continue_procedure_3
IS
I NUMBER := 0;
J NUMBER := 10;
K NUMBER := 0;
BEGIN
    <<out_loop>>
    WHILE I <= J LOOP
        I := I + 1;
        INSERT INTO continue_testing_table_3 VALUES('I' || TO_CHAR(I));

        <<in_loop>>
        WHILE K <= J * 2 LOOP
            K := K + 1;
            CONTINUE out_loop WHEN K > J / 2;
            INSERT INTO continue_testing_table_3 VALUES('K' || TO_CHAR(K));
        END LOOP in_loop;

        K := 0;
    END LOOP out_loop; 
END;

CALL continue_procedure_3();
SELECT * FROM continue_testing_table_3;

 CREATE OR REPLACE PROCEDURE continue_procedure_3 ()
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) := 0;
        J NUMBER(38, 18) := 10;
        K NUMBER(38, 18) := 0;
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<out_loop>> ***/!!!
        WHILE (:I <= :J) LOOP
            I := :I + 1;
                   INSERT INTO continue_testing_table_3
            VALUES('I' || NVL(TO_CHAR(:I) :: STRING, ''));
            !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<in_loop>> ***/!!!
            WHILE (:K <= :J * 2) LOOP
                K := :K + 1;
                IF (:K > :J / 2) THEN
                    CONTINUE out_loop;
                END IF;
                       INSERT INTO continue_testing_table_3
                VALUES('K' || NVL(TO_CHAR(:K) :: STRING, ''));
                   END LOOP in_loop;
            K := 0;
               END LOOP out_loop;
    END;
$$;

CALL continue_procedure_3();

SELECT * FROM
    continue_testing_table_3;