DECLARE

Translation reference to convert Oracle DECLARE statement to Snowflake Scripting

Some parts in the output code are omitted for clarity reasons.

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

IN -> Oracle_01.sql

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

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE var_decl_proc ()
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);
        var2 NUMBER(38, 18) := 1;
        var3 NUMBER(38, 18) := 1 /*** SSC-FDM-OR0025 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE ***/;
        var4 NUMBER(38, 18) DEFAULT 1;
        var5 NUMBER(38, 18) DEFAULT 1 /*** SSC-FDM-OR0025 - 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 ;