DECLARE

Translation reference to convert Oracle DECLARE statement to Snowflake Scripting

Description

Oracle DECLARE statement is an optional part of the PL/SQL block statement. It allows the creation of variables, constants, procedures declarations, and definitions, functions declarations, and definitions, exceptions, cursors, types, and many other statements. For more information regarding Oracle DECLARE, check here.

Oracle DECLARE Syntax

declare_section body

declare_section::= { item_list_1 [ item_list_2 ] | item_list_2 }

item_list_1::= 
{ type_definition
| cursor_declaration
| item_declaration
| function_declaration
| procedure_declaration
}
 ...
 
item_list_2::=
{ cursor_declaration
| cursor_definition
| function_declaration
| function_definition
| procedure_declaration
| procedure_definition
}
 ...

item_declaration::=
{ collection_variable_decl
| constant_declaration
| cursor_variable_declaration
| exception_declaration
| record_variable_declaration
| variable_declaration
}

body::= BEGIN statement ...
  [ EXCEPTION exception_handler [ exception_handler ]... ] END [ name ] ;

Snowflake Scripting DECLARE Syntax

[ DECLARE
  { <variable_declaration> | <cursor_declaration> | <exception_declaration> | <resultset_declaration> }
  [, { <variable_declaration> | <cursor_declaration> | <exception_declaration> | <resultset_declaration> } ... ]
]
BEGIN
    <statement>;
    [ <statement>; ... ]
[ EXCEPTION <exception_handler> ]
END [ <label> ] ;

Sample Source Patterns

Variable declaration

Oracle Variable Declaration Syntax

variable_declaration::= 
variable datatype [ [ NOT NULL] {:= | DEFAULT} expression ] ;

Snowflake Scripting Variable Declaration Syntax

<variable_name> <type>;

<variable_name> DEFAULT <expression> ;

<variable_name> <type> DEFAULT <expression> ;

Oracle

Query

CREATE OR REPLACE PROCEDURE var_decl_proc
IS
var1 NUMBER; 
var2 NUMBER := 1;
var3 NUMBER NOT NULL := 1;
var4 NUMBER DEFAULT 1;
var5 NUMBER NOT NULL DEFAULT 1;
BEGIN
    NULL; 
END;

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.var_decl_proc ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      var1 NUMBER ;
      var2 NUMBER := 1;
      var3 NUMBER := 1 /*** MSC-WARNING - MSCEWI3098 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE ***/;
      var4 NUMBER DEFAULT 1;
      var5 NUMBER DEFAULT 1 /*** MSC-WARNING - MSCEWI3098 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE ***/;
   BEGIN
      NULL;
   END;
$$;

Constant declaration

Constants are not supported in Snowflake Scripting, however, they are being transformed to variables to simulate the behavior.

Oracle Constant Declaration Syntax

constant_declaration::=
constant CONSTANT datatype [NOT NULL] { := | DEFAULT } expression ;

Snowflake Scripting Variable Declaration Syntax

<variable_name> <type>;

<variable_name> DEFAULT <expression> ;

<variable_name> <type> DEFAULT <expression> ; 

Oracle

Query

CREATE OR REPLACE PROCEDURE const_decl_proc
IS
my_const1 CONSTANT NUMBER := 40;
my_const2 CONSTANT NUMBER NOT NULL := 40;
my_const2 CONSTANT NUMBER DEFAULT 40;
my_const2 CONSTANT NUMBER NOT NULL DEFAULT 40;
BEGIN
    NULL; 
END;

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.const_decl_proc ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      /*** MSC-WARNING - MSCEWI1076 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE ***/
      my_const1 NUMBER := 40;
      /*** MSC-WARNING - MSCEWI1076 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE ***/
      /*** MSC-WARNING - MSCEWI3098 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE ***/
      my_const2 NUMBER := 40;
      /*** MSC-WARNING - MSCEWI1076 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE ***/
      my_const2 NUMBER DEFAULT 40;
      /*** MSC-WARNING - MSCEWI1076 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE ***/
      /*** MSC-WARNING - MSCEWI3098 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE ***/
      my_const2 NUMBER DEFAULT 40;
   BEGIN
      NULL;
   END;
$$;

Cursor declaration

Oracle Cursor Declaration Syntax

cursor_declaration::= CURSOR cursor
  [( cursor_parameter_dec [, cursor_parameter_dec ]... )]
    RETURN rowtype;

cursor_parameter_dec::= parameter [IN] datatype [ { := | DEFAULT } expression ]

rowtype::= 
{ {db_table_or_view | cursor | cursor_variable}%ROWTYPE
  | record%TYPE
  | record_type
  }

Snowflake Scripting Cursor Declaration Syntax

<cursor_name> CURSOR [ ( <argument> [, <argument> ... ] ) ]
        FOR <query> ;

The Oracle cursor declaration is not required so it might be commented out on the output code. The cursor definition will be used instead of and it will be converted to the Snowflake Scripting cursor declaration. Please go to the CURSOR section to get more information about cursor definition.

Exception declaration

The exception declaration sometimes could be followed by the exception initialization, the current transformation takes both and merge them into the Snowflake Scripting exception declaration. The original PRAGMA EXCEPTION_INIT will be commented out.

Oracle Exception Declaration Syntax

exception_declaration::= exception EXCEPTION;

PRAGMA EXCEPTION_INIT ( exception, error_code ) ;

Snowflake Scripting Exception Declaration Syntax

<exception_name> EXCEPTION [ ( <exception_number> , '<exception_message>' ) ] ;

Oracle

Query

CREATE OR REPLACE PROCEDURE procedure_exception
IS
my_exception EXCEPTION;
my_exception2 EXCEPTION;
PRAGMA EXCEPTION_INIT ( my_exception2, -20100 );
my_exception3 EXCEPTION;
PRAGMA EXCEPTION_INIT ( my_exception3, -19000 );
BEGIN
    NULL; 
END;

Snowflake Scripting

Query

CREATE OR REPLACE PROCEDURE PUBLIC.procedure_exception ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      my_exception EXCEPTION;
      my_exception2 EXCEPTION (-20100, '');
-- ** MSC-WARNING - MSCEWI3051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED **
--      PRAGMA EXCEPTION_INIT ( my_exception2, -20100 );
      my_exception3 EXCEPTION /*** MSC-WARNING - MSCEWI3099 - EXCEPTION CODE NUMBER EXCEEDS SNOWFLAKE SCRIPTING LIMITS ***/;
-- ** MSC-WARNING - MSCEWI3051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED **
--      PRAGMA EXCEPTION_INIT ( my_exception3, -19000 );
   BEGIN
      NULL;
   END;
$$;

Not supported cases

The next Oracle declaration statements are not supported by the Snowflake Scripting declaration block:

1. Cursor variable declaration.

2. Collection variable declaration.

3. Record variable declaration.

4. Type definition (all its variants).

5. Function declaration and definition.

6. Procedure declaration and definition.

Known issues

1. The variable declarations with NOT NULL constraints are not supported by Snow Scripting.

The creation of variables with NOT NULL constraint throws an error in Snow Scripting.

2. The cursor declaration has no equivalent to Snowflake Scripting.

The Oracle cursor declaration is useless so it might be commented out in the output code. The cursor definition will be used instead and it will be converted to the Snowflake Scripting cursor declaration.

3. The exception code exceeds Snowflake Scripting limits.

Oracle exception code is being removed when it exceeds the Snowflake Scripting code limits. The exception code must be an integer between -20000 and -20999.

3. The not supported cases.

There are some Oracle declaration statements that are not supported by the Snowflake Scripting declaration block, so it might be commented out and a warning will be added.

Related EWIS

1. MSCEWI1076: Constants are not supported by Snowflake Scripting. It was transformed into a variable.

2. MSCEWI3051: PRAGMA EXCEPTION_INIT Not Supported.

3. MSCEWI3098: The NOT NULL constraint is not supported in variable declarations inside procedures.

4. MSCEWI3099: The exception code exceeds Snowflake Scripting limits.