Procedures

Snowflake CREATE PROCEDURE is defined in SQL Syntax whereas its inner statements are defined in JavaScript.

Some parts in the output code are omitted for clarity reasons.

Example 1: Basic Procedure Conversion

Oracle

IN -> Oracle_01.sql

--Additional Params: -t JavaScript

CREATE OR REPLACE PROCEDURE PROC1
IS
BEGIN
null;
END;

Snowflake

OUT -> Oracle_01.sql

CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    // SnowConvert Helpers Code section is omitted.

    null;
$$;

Example 2: Procedure Conversion with basic statements: Declaration, Assignment, Cursor Declaration, FOR Cursor, Open, LOOP, CLOSE, IF,

Oracle

IN -> Oracle_02.sql

--Additional Params: -t JavaScript

CREATE OR REPLACE PROCEDURE PROC1
(
  param1 NUMBER
)
IS
  localVar1 NUMBER;
  countRows NUMBER;
  tempSql VARCHAR(100);
  tempResult NUMBER;
  CURSOR MyCursor
    IS
       SELECT COL1 FROM Table1;

BEGIN
    localVar1 := param1;
    countRows := 0;
    tempSql := 'SELECT COUNT(*) FROM Table1 WHERE COL1 =' || localVar1;

    FOR myCursorItem IN MyCursor
        LOOP
            localVar1 := myCursorItem.Col1;
            countRows := countRows + 1; 
        END LOOP;
    INSERT INTO Table2 VALUES(countRows, 'ForCursor: Total Row count is: ' || countRows);
    countRows := 0;

    OPEN MyCursor;
    LOOP
        FETCH MyCursor INTO tempResult;
        EXIT WHEN MyCursor%NOTFOUND;
        countRows := countRows + 1;
    END LOOP;
    CLOSE MyCursor;
    INSERT INTO Table2 VALUES(countRows, 'LOOP: Total Row count is: ' || countRows);

    EXECUTE IMMEDIATE tempSql INTO tempResult;
    IF tempResult > 0 THEN 
        INSERT INTO Table2 (COL1, COL2) VALUES(tempResult, 'Hi, found value:' || localVar1 || ' in Table1 -- There are ' || tempResult || ' rows');
        COMMIT;
    END IF;
END PROC1;

Snowflake

OUT -> Oracle_02.sql

--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE PROC1
(param1 FLOAT
)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  // REGION SnowConvert Helpers Code
  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 SQL = {
    FOUND : false,
    NOTFOUND : false,
    ROWCOUNT : 0,
    ISOPEN : false
  };
  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 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 FETCH_INTO_COLLECTIONS = function (collections,fetchValues) {
    for(let i = 0;i < collections.length;i++) {
      collections[i].push(fetchValues[i]);
    }
  };
  var IS_NULL = (arg) => !(arg || arg === 0);
  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 concatValue = (arg) => IS_NULL(arg) ? "" : arg;
  // END REGION

  let LOCALVAR1;
  let COUNTROWS;
  let TEMPSQL;
  let TEMPRESULT;
  let MYCURSOR = new CURSOR(`SELECT COL1 FROM
          Table1`,() => []);
  LOCALVAR1 = PARAM1;
  COUNTROWS = 0;
  TEMPSQL = `SELECT COUNT(*) FROM
   Table1
WHERE COL1 =${concatValue(LOCALVAR1)}`;
  MYCURSOR.OPEN();
  while ( MYCURSOR.NEXT() ) {
    let MYCURSORITEM = MYCURSOR.CURRENT;
    LOCALVAR1 = MYCURSORITEM.COL1;
    COUNTROWS = COUNTROWS + 1;
  }
  MYCURSOR.CLOSE();
  EXEC(`INSERT INTO Table2
    VALUES(?, 'ForCursor: Total Row count is: ' || NVL(? :: STRING, ''))`,[COUNTROWS,COUNTROWS]);
  COUNTROWS = 0;
  MYCURSOR.OPEN();
  while ( true ) {
    MYCURSOR.FETCH(TEMPRESULT) && ([TEMPRESULT] = MYCURSOR.INTO());
    if (MYCURSOR.NOTFOUND()) {
      break;
    }
    COUNTROWS = COUNTROWS + 1;
  }
  MYCURSOR.CLOSE();
  EXEC(`INSERT INTO Table2
    VALUES(?, 'LOOP: Total Row count is: ' || NVL(? :: STRING, ''))`,[COUNTROWS,COUNTROWS]);
  [TEMPRESULT] = EXEC(TEMPSQL);
  if (TEMPRESULT > 0) {
    // ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
    EXEC(`INSERT INTO Table2(COL1, COL2) VALUES(?, 'Hi, found value:' || NVL(? :: STRING, '') || ' in Table1 -- There are ' || NVL(? :: STRING, '') || ' rows')`,[TEMPRESULT,LOCALVAR1,TEMPRESULT]);
    EXEC(`--** SSC-FDM-OR0012 - COMMIT REQUIRES THE APPROPRIATE SETUP TO WORK AS INTENDED **
COMMIT;`);
  }
$$;

Call of procedures inside other procedure

Oracle

IN -> Oracle_03.sql

--Additional Params: -t JavaScript

CREATE OR REPLACE PROCEDURE PROCEDURE01(param1 NUMBER, param2 VARCHAR)
IS
BEGIN
INSERT INTO TABLE1 VALUES(param1, param2);
END;

CREATE OR REPLACE PROCEDURE PROCEDURE02(param1 NUMBER, param2 VARCHAR)
IS
BEGIN
PROCEDURE01(param1, param2);
END;

Snowflake

OUT -> Oracle_03.sql

CREATE OR REPLACE PROCEDURE PROCEDURE01 (param1 FLOAT, param2 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	// SnowConvert Helpers Code section is omitted.

	EXEC(`INSERT INTO TABLE1
	VALUES(?, ?)`,[PARAM1,PARAM2]);
$$;

CREATE OR REPLACE PROCEDURE PROCEDURE02 (param1 FLOAT, param2 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
	// SnowConvert Helpers Code section is omitted.

	EXEC(`CALL
	PROCEDURE01(?, ?)`,[PARAM1,PARAM2]);
$$;

Known Issues

No issues were found.

Related EWIs

1. SSC-EWI-0022: One or more identifiers in this statement were considered parameters by default.

2. SSC-FDM-OR0012: COMMIT and ROLLBACK statements require adequate setup to perform as intended.