Procedures
Snowflake CREATE PROCEDURE is defined in SQL Syntax whereas its inner statements are defined in JavaScript.
Last updated
Snowflake CREATE PROCEDURE is defined in SQL Syntax whereas its inner statements are defined in JavaScript.
Last updated
Example 1: Basic Procedure Conversion
Oracle
--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE PROC1
IS
BEGIN
null;
END;
Snowflake
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
--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
--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;`);
}
$$;
Oracle
--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
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]);
$$;
SSC-EWI-0022: One or more identifiers in this statement were considered parameters by default.
SSC-FDM-OR0012: COMMIT and ROLLBACK statements require adequate setup to perform as intended.