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) {