COMPOUND STATEMENTS

This section is a translation specification for the compound statements

This section is a work in progress, information may change in the future.

General description

The basic unit of a PL/SQL source program is the block, which groups related declarations and statements.
A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required. (PL/SQL Anonymous Blocks)

The BEGIN...END block in Oracle can have the following characteristics:

Be nested.
Contain the DECLARE statement for variables.
Group multiple SQL or PL/SQL statements.

Oracle syntax

[DECLARE <Variable declaration>]
BEGIN
  <Executable statements>
[EXCEPTION <Exception handler>]
END

Snowflake syntax

BEGIN
    <statement>;
    [ <statement>; ... ]
[ EXCEPTION <exception_handler> ]
END;

In Snowflake, a BEGIN/END block can be the top-level construct inside an anonymous block (Snowflake documentation).

Sample Source Patterns

1. IF-ELSE block

Review the following documentation about IF statements to learn more: SnowConvert IF statements translation and Snowflake IF statement documentation

Oracle

   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;

Statement processed.
You are an adult.

Snowflake

When calling a procedure or user-defined function (UDF), generating code is needed to support the equivalence as call_results variable. In this case, is used to print the information.

Review the user-defined function (UDF) used here.

DECLARE
   age NUMBER(38, 18) := 18;
   call_results VARIANT;
BEGIN
    IF ( :age >= 18) THEN
        --** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_OUTPUT.PUT_LINE' INSERTED. **
        --** MSC-WARNING - MSCEWI3115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. **
        call_results := (
            CALL DBMS_OUTPUT.PUT_LINE('You are an adult.')
        );
    ELSE 
        --** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_OUTPUT.PUT_LINE' INSERTED. **
        --** MSC-WARNING - MSCEWI3115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. **
            call_results := (
            CALL DBMS_OUTPUT.PUT_LINE('You are a minor')
        );
    END IF;
    
    RETURN call_results;
END;

anonymous block
You are an adult.

2. CASE statement

For more information, review the following documentation: SnowConvert CASE statement documentation and Snowflake CASE documentation

Oracle

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;

Statement processed.
Tuesday

Snowflake

When calling a procedure or user-defined function (UDF), generating code is needed to support the equivalence as call_results variable. In this case, is used to print the information.

Review the user-defined function (UDF) used here.

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

anonymous block
Tuesday

3. LOOP statements

For more information review the following documentation: SnowConvert FOR LOOP and Snowflake LOOP documentation and FOR documentation.

Oracle

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

Statement processed.

Snowflake

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

anonymous block

4. Procedure call and OUTPUT parameters

Anonymous block in Oracle may have calls to procedures. Furthermore, the following documentation may be useful: SnowConvert Procedure documentation.

The following example uses the OUT parameters, the information about the current transformation can be found here: SnowConvert OUTPUT Parameters

Oracle

-- 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;
/

Statement processed.
The sum of 10 and 20 is 30

Snowflake

-- Procedure declaration
CREATE OR REPLACE PROCEDURE calculate_sum (p_num1 NUMBER(38, 18), p_num2 NUMBER(38, 18), p_result
--** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED **
NUMBER(38, 18)
)
RETURNS VARIANT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
    -- Calculate the sum of the two numbers
        p_result := :p_num1 + :p_num2;
        --** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING **
        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)
    );
    --** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING **
    v_result := :call_results;

    -- Display the result
    --** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_OUTPUT.PUT_LINE' INSERTED. **
    --** MSC-WARNING - MSCEWI3115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. **
    call_results := (
        CALL
        DBMS_OUTPUT.PUT_LINE('The sum of ' || NVL(:v_num1 :: STRING, '') || ' and ' || NVL(:v_num2 :: STRING, '') || ' is ' || NVL(:v_result :: STRING, ''))
    );
    RETURN call_results;
END;

anonymous block
The sum of 10 and 20 is 30

5. Alter session

For more information, review the following documentation: Alter session documentation.

Notice that in Oracle, the block BEGIN...END should use the EXECUTE IMMEDIATE statement to run alter session statements.

Oracle

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;

Statement processed.
Done

Snowflake

The following warning may be added in the future: /*** MSC-WARNING - MSCEWI3058 - NLS_DATE_FORMAT SESSION PARAMETER DOES NOT ENFORCE THE INPUT FORMAT IN ORACLE ***/

DECLARE
     lv_sql_txt VARCHAR(200) /*** MSC-WARNING - MSCEWI1036 - VARCHAR2 DATA TYPE CONVERTED TO VARCHAR ***/;
BEGIN
     lv_sql_txt := 'ALTER SESSION SET nls_date_format = ''DD-MM-YYYY''';
--     --** MSC-ERROR - MSCEWI1027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED **
--     EXECUTE IMMEDIATE :lv_sql_txt
                                  ;
END

anonymous block
Done

6. Cursors

The following example displays the usage of a cursor inside a BEGIN...END block. Review the following documentation to learn more: Cursor documentation.

Oracle

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;

Statement processed.
NameA NameZ

Snowflake

When calling a procedure or user-defined function (UDF), generating code is needed to support the equivalence as call_results variable. In this case, is used to print the information.

Review the user-defined function (UDF) used here.

CREATE OR REPLACE TABLE employee (
       ID_Number NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
       emp_Name	VARCHAR(200),
       emp_Phone NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/
   );

   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 emp
    ORDER BY emp.ID_Number;
    call_results VARIANT; -- variable added to call UDF
BEGIN
    OPEN cursor1;
    FETCH cursor1 INTO
        :var1;
    CLOSE cursor1;
    --** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_OUTPUT.PUT_LINE' INSERTED. **
    --** MSC-WARNING - MSCEWI3115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. **
    call_results := ( CALL DBMS_OUTPUT.PUT_LINE(:var1));
    RETURN call_results;
END;

anonymous block
NameA NameZ

7. Select statements

For more information review the following documentation: Select documentation.

Oracle

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;

Statement processed.
2

Snowflake

When calling a procedure or user-defined function (UDF), generating code is needed to support the equivalence as call_results variable. In this case, is used to print the information.

Review the user-defined function (UDF) used here.

CREATE OR REPLACE TABLE employee (
       ID_Number NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
       emp_Name	VARCHAR(200),
       emp_Phone NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/
   );

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; -- variable added to call UDF
BEGIN
    SELECT COUNT(*) INTO
        :var_Result
    FROM
        employee;
    --** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_OUTPUT.PUT_LINE' INSERTED. **
    --** MSC-WARNING - MSCEWI3115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. **
    call_results := ( CALL DBMS_OUTPUT.PUT_LINE(:var_Result));
    RETURN call_results;
END;

anonymous block
2

8. Join Statements

For more information review the following documentation: Joins documentation.

Oracle

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;

Statement processed.
7

Snowflake

When calling a procedure or user-defined function (UDF), generating code is needed to support the equivalence as call_results variable. In this case, is used to print the information.

Review the user-defined function (UDF) used here.

CREATE OR REPLACE TABLE t1 (col1 INTEGER);

CREATE OR REPLACE 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;
    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;
    --** MSC-WARNING - MSCCP0003 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP **
    FOR rec IN cursor1 DO
        LET rec OBJECT := rec.sc_cursor_record;
        total_price :=
        --** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN AproxNumeric AND unknown **
        :total_price + rec.FIRSTTABLE;
    END FOR;
    CLOSE cursor1;
    --** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_OUTPUT.PUT_LINE' INSERTED. **
    --** MSC-WARNING - MSCEWI3115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. **
    call_results := (
        CALL

        DBMS_OUTPUT.PUT_LINE(:total_price)
    );
    RETURN call_results;
END;

9. Exception handling

Oracle

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

Statement processed.
ORA-01476: divisor is equal to zero

Snowflake

ZERO_DIVIDE exception in Snowflake is not supported.

DECLARE
      v_result NUMBER(38, 18);
      error_results VARIANT;
BEGIN
      v_result := 1 / 0;
   EXCEPTION
         WHEN ZERO_DIVIDE THEN
               --** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_OUTPUT.PUT_LINE' INSERTED. **
               --** MSC-WARNING - MSCEWI3115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. **
               error_results := (
                     CALL
                     DBMS_OUTPUT.PUT_LINE( SQLERRM )
               );
               RETURN error_results;
END;

anonymous block
Division by zero

Known issues

Unsupported GOTO statements in Oracle. Review this page.
Exceptions that use GOTO statements may be affected too.
Cursor functionality may be adapted under current restrictions on translations.

PreviousRecord Type Definition NextCONTINUE

Last updated 5 months ago