PACKAGE VARIABLES can be declared either in the package declaration or in the PACKAGE BODY. Due to its behavior, these variables are converted into Snowflake session variables so each usage or assignment is translated to its equivalent in Snowflake.
Package variable usages are transformed into the Snowflake GETVARIABLE function which accesses the current value of a session variable. An explicit cast is added to the original variable data type in order to maintain the functional equivalence in the operations where these variables are used.
Oracle
CREATEORREPLACE PACKAGE PKG1 ASPROCEDURE procedure1; package_variable NUMBER:=100;END PKG1;CREATEORREPLACE PACKAGE BODY PKG1 ASPROCEDURE procedure1 ASBEGININSERT INTO TABLE1(ID) VALUES(package_variable);END;END PKG1;CALL SCHEMA1.PKG1.procedure1();SELECT*FROM TABLE1;
|ID |
|---|
|100|
Snowflake
CREATESCHEMAIFNOTEXISTS SCHEMA1_PKG1;/*** MSC-WARNING - MSCEWI3034 - PACKAGE PROCEDURE DECLARATION HEADING IS NOT NECESSARY IN SNOWFLAKE ***//* PROCEDURE procedure1;*/SET"SCHEMA1_PKG1.PACKAGE_VARIABLE"=''|| (100);CREATEORREPLACEPROCEDURE SCHEMA1_PKG1.procedure1 ()RETURNSVARCHARLANGUAGESQLEXECUTEASCALLERAS$$BEGININSERT INTO SCHEMA1.TABLE1(ID) VALUES(GETVARIABLE('SCHEMA1_PKG1.PACKAGE_VARIABLE') :: NUMBER);END;$$;CALL SCHEMA1_PKG1.procedure1();SELECT*FROMPUBLIC.TABLE1;
|ID |
|---|
|100|
Variable regular assignment
When a package variable is assigned using the := operator, the assignation is replaced by a SnowConvert UDF called UPDATE_PACKAGE_VARIABLE_STATE which is an abstraction of the Snowflake SETVARIABLE function.
CREATESCHEMAIFNOTEXISTS PKG1;----** MSC-WARNING - MSCEWI3034 - PACKAGE PROCEDURE DECLARATION HEADING IS NOT NECESSARY IN SNOWFLAKE **--PROCEDURE procedure1;SET"PKG1.PACKAGE_VARIABLE"=''|| (100);/*** MSC-INFORMATION - MSCINF0054 - PACKAGE BODY CONVERSION RATE IS AFFECTED BY ITS INNER ELEMENTS ***/--** MSC-WARNING - MSCEWI1050 - MISSING DEPENDENT OBJECT "TABLE1" **CREATEORREPLACEPROCEDURE PKG1.procedure1 ()RETURNSVARCHARLANGUAGESQLEXECUTEASCALLERAS$$BEGIN--** MSC-WARNING - MSCEWI3122 - PACKAGE VARIABLE ASSIGNMENT WAS TRANSFORMED TO CUSTOM UDF ** CALL UPDATE_PACKAGE_VARIABLE_STATE_UDF('PKG1.PACKAGE_VARIABLE', TO_VARCHAR(GETVARIABLE('PKG1.PACKAGE_VARIABLE') :: NUMBER + 100));
INSERT INTO TABLE1(ID) VALUES(GETVARIABLE('PKG1.PACKAGE_VARIABLE') :: NUMBER);END;$$;CALL PKG1.procedure1();--** MSC-WARNING - MSCEWI1050 - MISSING DEPENDENT OBJECT "TABLE1" **SELECT*FROM TABLE1;
|ID |
|---|
|200|
Variable assignment as an output argument
When a package variable is used as an output argument a new variable is declared inside the procedure, this variable will catch the output argument value of the procedure, and then the variable will be used to update the session variable which refers to the package variable using the UPDATE_PACKAGE_VARIABLE_STATE mentioned above. For more information check de output parameters transformation.
CREATESCHEMAIFNOTEXISTS PKG1;----** MSC-WARNING - MSCEWI3034 - PACKAGE PROCEDURE DECLARATION HEADING IS NOT NECESSARY IN SNOWFLAKE **--PROCEDURE procedure1;----** MSC-WARNING - MSCEWI3034 - PACKAGE PROCEDURE DECLARATION HEADING IS NOT NECESSARY IN SNOWFLAKE **-- PROCEDURE procedure2(out_param OUT NUMBER);SET"PKG1.PACKAGE_VARIABLE"=''|| (100);/*** MSC-INFORMATION - MSCINF0054 - PACKAGE BODY CONVERSION RATE IS AFFECTED BY ITS INNER ELEMENTS ***/--** MSC-WARNING - MSCEWI1050 - MISSING DEPENDENT OBJECT "TABLE1" **CREATEORREPLACEPROCEDURE PKG1.procedure1 ()RETURNSVARCHARLANGUAGESQLEXECUTEASCALLERAS$$DECLARE PKG1_PACKAGE_VARIABLE VARIANT; call_results VARIANT;BEGIN call_results := (CALL PKG1. procedure2(:PKG1_PACKAGE_VARIABLE) );--** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING ** PKG1_PACKAGE_VARIABLE := :call_results;--** MSC-WARNING - MSCEWI3122 - PACKAGE VARIABLE ASSIGNMENT WAS TRANSFORMED TO CUSTOM UDF **CALL UPDATE_PACKAGE_VARIABLE_STATE_UDF('PKG1.PACKAGE_VARIABLE', TO_VARCHAR(:PKG1_PACKAGE_VARIABLE));INSERT INTO TABLE1(ID) VALUES(GETVARIABLE('PKG1.PACKAGE_VARIABLE') :: NUMBER);END;$$;CREATEORREPLACEPROCEDURE PKG1.procedure2 (out_param--** MSC-WARNING - MSCEWI1083 - OUTPUT PARAMETERS ARE NOT SUPPORTED BUT FUNCTIONALITY IS BEING EMULATED **NUMBER(38, 18))RETURNS VARIANTLANGUAGESQLEXECUTEASCALLERAS$$BEGIN out_param :=1000;--** MSC-WARNING - MSCEWI1082 - AUTO-GENERATED CODE IN ORDER TO SUPPORT OUT PARAMETERS IN SNOWFLAKE SCRIPTING **RETURNnull;END;$$;CALL PKG1.procedure1();