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.
CREATE OR REPLACE TABLE table1 (id 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"}}'
;
Variable declaration
Oracle
IN -> Oracle_02.sql
CREATEORREPLACE PACKAGE PKG1 AS package_variable NUMBER:=100;END PKG1;
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
IN -> Oracle_03.sql
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;
Note that the PROCEDURE definition in the package is removed since it is not required by Snowflake.
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.