SnowConvert: Oracle PL/SQL to JavaScript Helpers¶

 var BetweenFunc = function (expression,startExpr,endExpr) {
   if ([expression,startExpr,endExpr].some((arg) => arg == null)) {
      return null;
   }
   return expression >= startExpr && expression <= endExpr;
};

 var concatValue = (arg) => IS_NULL(arg) ? "" : arg;

 var FETCH_INTO_COLLECTIONS = function (collections,fetchValues) {
   for(let i = 0;i < collections.length;i++) {
      collections[i].push(fetchValues[i]);
   }
};   
var CURSOR = function (stmt,binds,isRefCursor,isOut) {
   var statementObj, result_set, total_rows, ISOPEN = false, result_set_table = '', self = this, row_count, found;
   this.CURRENT = new Object;
   this.INTO = function () {
         return self.res;
      };
   this.OPEN = function (openParameters) {
         if (ISOPEN && !isRefCursor) RAISE(-6511,"CURSOR_ALREADY_OPEN","cursor already open");
         var finalStmt = openParameters && openParameters.query || stmt;
         var parameters = openParameters && openParameters.binds || [];
         var finalBinds = binds instanceof Function ? binds(...parameters) : binds;
         finalBinds = finalBinds || parameters;
         try {
            if (isOut) {
               if (!temptable_prefix) {
                  temptable_prefix = `${procname}_TEMP_${(EXEC(`select current_session() || '_' || to_varchar(current_timestamp, 'yyyymmddhh24missss')`,{
                        sql : 0
                     }))[0]}_`;
               }
               if (!result_set_table) {
                  result_set_table = temptable_prefix + outCursorResultNumber++;
                  EXEC(`CREATE OR REPLACE TEMPORARY TABLE ${result_set_table} AS ${finalStmt}`,{
                     sql : 0
                  });
               }
               finalStmt = "SELECT * FROM " + result_set_table
            }
            [result_set,statementObj,total_rows] = [EXEC(finalStmt,finalBinds,{
                  sql : 0,
                  row : 2
               }),_RS,_RS.getColumnCount()]
            ISOPEN = true;
            row_count = 0;
         } catch(error) {
            RAISE(error.code,"error",error.message);
         }
         return this;
      };
   this.NEXT = function () {
         if (total_rows && result_set.next()) {
            this.CURRENT = new Object;
            for(let i = 1;i <= statementObj.getColumnCount();i++) {
               (this.CURRENT)[statementObj.getColumnName(i)] = result_set.getColumnValue(i);
            }
            return true;
         } else return false;
      };
   this.FETCH = function (record) {
         var recordKeys = record ? Object.keys(record) : undefined;
         self.res = [];
         if (!ISOPEN) RAISE(-1001,"INVALID_CURSOR","invalid cursor");
         if (recordKeys && recordKeys.length != statementObj.getColumnCount()) RAISE(-6504,"ROWTYPE_MISMATCH","Return types of Result Set variables or query do not match");
         self.res = fetch(statementObj,result_set);
         if (self.res && self.res.length > 0) {
            found = true;
            row_count++;
            if (recordKeys) {
               for(let i = 0;i < self.res.length;i++) {
                  record[recordKeys[i]] = (self.res)[i];
               }
               return false;
            }
            return true;
         } else found = false;
         return false;
      };
   this.CLOSE = function () {
         if (!ISOPEN) RAISE(-1001,"INVALID_CURSOR","invalid cursor");
         found = row_count = result_set_table = total_rows = result_set = statementObj = undefined;
         ISOPEN = false;
      };
   this.FETCH_BULK_COLLECT_INTO = function (variables,limit) {
         if (variables.length != statementObj.getColumnCount()) RAISE(-6504,"ROWTYPE_MISMATCH","Return types of Result Set variables or query do not match");
         if (limit) {
            for(let i = 0;i < limit && this.FETCH();i++)FETCH_INTO_COLLECTIONS(variables,self.res);
         } else {
            while ( this.FETCH() )
               FETCH_INTO_COLLECTIONS(variables,self.res);
         }
      };
   this.FOUND = () => ISOPEN ? typeof(found) == "boolean" ? found : null : RAISE(-1001,"INVALID_CURSOR","invalid cursor");
   this.NOTFOUND = () => ISOPEN ? typeof(found) == "boolean" ? !found : null : RAISE(-1001,"INVALID_CURSOR","invalid cursor");
   this.ROWCOUNT = () => ISOPEN ? row_count : RAISE(-1001,"INVALID_CURSOR","invalid cursor");
   this.ISOPEN = () => ISOPEN;
   this.SAVE_STATE = function () {
         return {
            tempTable : result_set_table,
            position : row_count
         };
      };
   this.RESTORE_STATE = function (tempTable,position) {
         result_set_table = tempTable
         if (result_set_table) {
            isOut = true
            this.OPEN();
            for(let i = 0;i < position;i++)this.FETCH();
         }
      };
   this.ROWTYPE = () => ROWTYPE(stmt,binds());
};
var outCursorResultNumber = 0;

 var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
var fixBind = function (arg) {
   arg = arg instanceof Date ? formatDate(arg) : IS_NULL(arg) ? null : arg;
   return arg;
};
var _RS, _ROWS, SQLERRM = "normal, successful completion", SQLCODE = 0;
var getObj = (_rs) => Object.assign(new Object(),_rs);
var getRow = (_rs) => (values = Object.values(_rs)) && (values = values.splice(-1 * _rs.getColumnCount())) && values;
var fetch = (_RS,_ROWS,fmode) => _RS.getRowCount() && _ROWS.next() && (fmode ? getObj : getRow)(_ROWS) || (fmode ? new Object() : []);

var EXEC = function (stmt,binds,opts) {
   try {
      binds = !(arguments[1] instanceof Array) && ((opts = arguments[1]) && []) || (binds || []);
      opts = opts || new Object();
      binds = binds ? binds.map(fixBind) : binds;
      _RS = snowflake.createStatement({
            sqlText : stmt,
            binds : binds
         });
      _ROWS = _RS.execute();
      if (opts.sql !== 0) {
         var isSelect = stmt.toUpperCase().trimStart().startsWith("SELECT");
         var affectedRows = isSelect ? _RS.getRowCount() : _RS.getNumRowsAffected();
         SQL.FOUND = affectedRows != 0;
         SQL.NOTFOUND = affectedRows == 0;
         SQL.ROWCOUNT = affectedRows;
      }
      if (opts.row === 2) {
         return _ROWS;
      }
      var INTO = function (opts) {
         if (opts.vars == 1 && _RS.getColumnCount() == 1 && _ROWS.next()) {
            return _ROWS.getColumnValue(1);
         }
         if (opts.rec instanceof Object && _ROWS.next()) {
            var recordKeys = Object.keys(opts.rec);
            Object.assign(opts.rec,Object.fromEntries(new Map(getRow(_ROWS).map((element,Index) => [recordKeys[Index],element]))))
            return opts.rec;
         }
         return fetch(_RS,_ROWS,opts.row);
      };
      var BULK_INTO_COLLECTION = function (into) {
         for(let i = 0;i < _RS.getRowCount();i++) {
            FETCH_INTO_COLLECTIONS(into,fetch(_RS,_ROWS,opts.row));
         }
         return into;
      };
      if (_ROWS.getRowCount() > 0) {
         return _ROWS.getRowCount() == 1 ? INTO(opts) : BULK_INTO_COLLECTION(opts);
      }
   } catch(error) {
      RAISE(error.code,error.name,error.message)
   }
};

  var SQL = {
  FOUND : false,
  NOTFOUND : false,
  ROWCOUNT : 0,
  ISOPEN : false
 };

 var IS_NULL = (arg) => !(arg || arg === 0);

 function LIKE(expr,pattern,esc,cs) {
   function fixPattern(pattern,esc) {
      const specials = '/.*+?|(){}[]\\'.split('');
      var newPattern = "";
      var fix = (c) => specials.includes(c) ? '\\' + c : c;
      for(var i = 0;i < pattern.length;i++) {
         var c = pattern[i];
         if (c === esc) {
            newPattern += pattern[i + 1]
            i++
         } else if (c === '%') {
            newPattern += ".*?"
         } else if (c === '_') {
            newPattern += "."
         } else if (c === '[' || ']') {
            newPattern += c
         } else newPattern += fix(c)
      }
      return newPattern;
   }
   return new RegExp(`^${fixPattern(pattern,esc)}$`,cs ? '' : 'i').exec(expr) != null;
}

 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) {
            saveVar(key,(this.cache)[key]);
         }
      }
   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) {
            saveVar(prop,value);
         }
      }
   });
   return rsProxy;
};
var PACKAGE_VARIABLES = new StateManager("PACKAGE_VARIABLES",true);

 var PACKAGE_VARIABLES = new StateManager("PACKAGE_VARIABLES",true);

 var RAISE = function (code,name,message) {
    message === undefined && ([name,message] = [message,name])
    var error = new Error(message);
    error.name = name
    SQLERRM = `${(SQLCODE = (error.code = code))}: ${message}`
    throw error;
};

 var ROWTYPE = (stmt, binds = [], obj = new Object()) => {
      EXEC(`SELECT * FROM (${stmt}) LIMIT 0`,binds);
      for(let i = 1;i <= _RS.getColumnCount();i++)obj[_ROWS.getColumnName(i)] = null;
      return obj;
   };