This section is a translation specification for the compound statements
This section is a work in progress, information may change in the future.
Some parts in the output code are omitted for clarity reasons.
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
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.
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.
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
IN -> Oracle_04.sql
-- 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
OUT -> Oracle_04.sql
-- 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;
Notice that in Oracle, the block BEGIN...END should use the EXECUTE IMMEDIATE statement to run alter session statements.
Oracle
IN -> Oracle_05.sql
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 ***/
OUT -> Oracle_05.sql
DECLARE
lv_sql_txt VARCHAR(200);
BEGIN
lv_sql_txt := 'ALTER SESSION SET nls_date_format = ''DD-MM-YYYY''';
!!!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;
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
IN -> Oracle_06.sql
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.
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;
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.
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;
anonymous block
2
8. Join Statements
For more information review the following documentation: Joins documentation.
Oracle
IN -> Oracle_08.sql
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.
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
LET rec OBJECT := rec.sc_cursor_record;
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;
9. Exception handling
Oracle
IN -> Oracle_09.sql
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.
OUT -> Oracle_09.sql
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;
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.
Related EWIs
SSC-EWI-0027:The following statement uses a variable/literal with an invalid query and it will not be executed.
