Package variables helper

Since packages are not supported in snowflake, variables inside them are transformed in session variables. This section shows how are those variables being handled.

Package variables Helper Function Definition

Helper depends on IS NULL helper.

When a package variable is used inside a procedure, the following helper will be generated:

When a package variable is used inside a procedure, the following helper will be generated:

function StateManager(packageName,keepInCache) {
   function getTypeChar(arg) {
      if (arg instanceof Date) {
         return "&";
      } else if (typeof arg == "number") {
         return "#";
      } else if (IS_NULL(arg)) {
         return "~";
      } else {
         return "$";
   function deserialize(arg) {
      if (arg === null) return undefined;
      let prefix = arg[0];
      let rest = arg.substr(1);
      switch(prefix) {
         case "&":return new Date(rest);
         case "#":return parseFloat(rest);
         case "$":return rest;
         case "~":return undefined;
         default:return arg;
   function saveVar(varName,value) {
      let varPackageName = `${packageName}.${varName}`;
      let fixedValue = `${getTypeChar(value)}${fixBind(value)}`;
      EXEC("SELECT SETVARIABLE(?,?)",[varPackageName,fixedValue]);
   function readVar(varName) {
      let varPackageName = `${packageName}.${varName}`;
      return deserialize((EXEC("SELECT GETVARIABLE(?)",[varPackageName]))[0]);
   this.saveState = function () {
         let keys = Object.keys(this.cache);
         for(let key of keys) {
   this.cache = new Object();
   let c = this.cache;
   let rsProxy = new Proxy(this,{
      get : function (target,prop,receiver) {
         if (!target[prop]) {
            c[prop] === undefined && (c[prop] = readVar(prop));
            return c[prop];
         return Reflect.get(...arguments);
      set : function (target,prop,value) {
         if (target[prop]) return;
         c[prop] = value;
         if (!keepInCache) {
   return rsProxy;

A helper instance is created for each package used to access its variables. Variables will be qualified with the name of the package if they are not qualified with it.

At the end of the procedure, the state of the variables used will be saved using the helper.

Note that in the following statement, name of the variable will change to match the package name:


Last updated