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.
CREATE OR REPLACE PROCEDURE var_decl_procISvar1 NUMBER; var2 NUMBER :=1;var3 NUMBER NOT NULL :=1;var4 NUMBER DEFAULT 1;var5 NUMBER NOT NULL DEFAULT 1;BEGINNULL; END;
Snowflake Scripting
OUT -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE var_decl_proc ()RETURNS VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ 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.
CREATE OR REPLACE PROCEDURE const_decl_procISmy_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;BEGINNULL; END;
Snowflake Scripting
OUT -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE const_decl_proc ()RETURNS VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ DECLARE--** SSC-FDM-0016 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE ** my_const1 NUMBER(38, 18) :=40;--** SSC-FDM-0016 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE **--** SSC-FDM-OR0025 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE ** my_const2 NUMBER(38, 18) :=40;--** SSC-FDM-0016 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE ** my_const2 NUMBER(38, 18) DEFAULT 40;--** SSC-FDM-0016 - CONSTANTS ARE NOT SUPPORTED BY SNOWFLAKE SCRIPTING. IT WAS TRANSFORMED TO A VARIABLE **--** SSC-FDM-OR0025 - NOT NULL CONSTRAINT IS NOT SUPPORTED BY SNOWFLAKE ** my_const2 NUMBER(38, 18) DEFAULT 40; BEGIN NULL; END;$$;
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 PRAGMAEXCEPTION_INIT will be commented out.
CREATE OR REPLACE PROCEDURE procedure_exception ()RETURNS VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ DECLARE my_exception EXCEPTION; my_exception2 EXCEPTION (-20100, ''); !!!RESOLVE EWI!!! /*** SSC-EWI-OR0051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED ***/!!! PRAGMA EXCEPTION_INIT ( my_exception2, -20100 ); !!!RESOLVE EWI!!! /*** SSC-EWI-OR0099 - EXCEPTION CODE NUMBER EXCEEDS SNOWFLAKE SCRIPTING LIMITS ***/!!! my_exception3 EXCEPTION; !!!RESOLVE EWI!!! /*** SSC-EWI-OR0051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED ***/!!!PRAGMA EXCEPTION_INIT ( my_exception3, -19000 ); BEGINNULL; END;$$;
Not supported cases
The next Oracle declaration statements are not supported by the Snowflake Scripting declaration block:
Cursor variable declaration.
Collection variable declaration.
Record variable declaration.
Type definition (all its variants).
Function declaration and definition.
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.