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)
}
};
Usage Samples
For all the samples, SnowConvert helpers Code were removed. You can find them here.
The following code examples illustrates how EXEC works.
EXEC simple case
Oracle
IN -> Oracle_01.sql
--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE EXECUTE_PROC AS
BEGIN
--CREATES HARDWARE TABLE WITH COLUMNS ID, DEVICE AND COLOR
--THIS IS AN EXECUTE IMMEDIATE JUST WITH AN STATEMENT
EXECUTE IMMEDIATE 'CREATE TABLE HARDWARE (ID NUMBER, DEVICE VARCHAR2(15), COLOR VARCHAR(15))';
END;
Snowflake
OUT -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE EXECUTE_PROC ()
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.
//CREATES HARDWARE TABLE WITH COLUMNS ID, DEVICE AND COLOR
//THIS IS AN EXECUTE IMMEDIATE JUST WITH AN STATEMENT
EXEC(`CREATE OR REPLACE TABLE HARDWARE (ID NUMBER(38, 18),
DEVICE VARCHAR(15),
COLOR VARCHAR(15))`);
$$;
EXEC with bindings
Oracle
IN -> Oracle_02.sql
--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE EXECUTE_PROC AS
ID_VAR NUMBER;
DEVICE_VAR VARCHAR2(15);
DEV_COLOR VARCHAR2(15);
COLOR_VAR VARCHAR2(15);
BEGIN
--EXEC WITH BINDINGS
--INSERTS A ROW WITH | 12 | MOUSE | BLACK | VALUES USING DIRECT BINDING FOR MOUSE
EXECUTE IMMEDIATE 'INSERT INTO HARDWARE VALUES (12, :MOUSE, ''BLACK'')' USING 'MOUSE';
--INSERTS A ROW WITH | 13 | KEYBOARD | WHITE | VALUES USING DIRECT BINDING FOR 13 AND KEYBOARD
EXECUTE IMMEDIATE 'INSERT INTO HARDWARE VALUES (:ID, :KEYBOARD, ''WHITE'')' USING 13, 'KEYBOARD';
--INSERTS A ROW WITH | 14 | HEADSET | GRAY | VALUES USING BINDING VARIABLES
ID_VAR := 14;
DEVICE_VAR := 'HEADSET';
COLOR_VAR := 'GRAY';
EXECUTE IMMEDIATE 'INSERT INTO HARDWARE VALUES (:DEV_ID, :DEV_VAR, :DEV_COLOR)' USING ID_VAR, DEVICE_VAR, COLOR_VAR;
END;
Snowflake
OUT -> Oracle_02.sql
CREATE OR REPLACE PROCEDURE EXECUTE_PROC ()
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.
let ID_VAR;
let DEVICE_VAR;
let DEV_COLOR;
let COLOR_VAR;
//EXEC WITH BINDINGS
//INSERTS A ROW WITH | 12 | MOUSE | BLACK | VALUES USING DIRECT BINDING FOR MOUSE
EXEC(`INSERT INTO HARDWARE
VALUES (12, ?, 'BLACK')`,[`MOUSE`]);
//INSERTS A ROW WITH | 13 | KEYBOARD | WHITE | VALUES USING DIRECT BINDING FOR 13 AND KEYBOARD
EXEC(`INSERT INTO HARDWARE
VALUES (?, ?, 'WHITE')`,[13,`KEYBOARD`]);
//INSERTS A ROW WITH | 14 | HEADSET | GRAY | VALUES USING BINDING VARIABLES
ID_VAR = 14;
DEVICE_VAR = `HEADSET`;
COLOR_VAR = `GRAY`;
EXEC(`INSERT INTO HARDWARE
VALUES (?, ?, ?)`,[ID_VAR,DEVICE_VAR,COLOR_VAR]);
$$;
EXEC with options
Oracle
IN -> Oracle_03.sql
--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE EXECUTE_PROC AS
BEGIN
--STORES THE ID INTO ID_VAR
EXECUTE IMMEDIATE 'SELECT ID FROM HARDWARE WHERE COLOR = ''BLACK''' INTO ID_VAR;
DBMS_OUTPUT.PUT_LINE(ID_VAR);
--STORES THE ID AND DEVICE INTO ID_VAR AND DEV_VAR, USING BINDING FOR COLOR
COLOR_VAR := 'BLACK';
EXECUTE IMMEDIATE 'SELECT ID, DEVICE FROM HARDWARE WHERE COLOR = :DEV_COLOR' INTO ID_VAR, DEVICE_VAR USING COLOR_VAR;
DBMS_OUTPUT.PUT_LINE(ID_VAR || ' ' || DEVICE_VAR);
END;
Snowflake
OUT -> Oracle_03.sql
CREATE OR REPLACE PROCEDURE EXECUTE_PROC ()
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.
//STORES THE ID INTO ID_VAR
[ID_VAR] = EXEC(`SELECT ID FROM
HARDWARE
WHERE COLOR = 'BLACK'`);
EXEC(`--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
CALL DBMS_OUTPUT.PUT_LINE_UDF(ID_VAR)`);
//STORES THE ID AND DEVICE INTO ID_VAR AND DEV_VAR, USING BINDING FOR COLOR
COLOR_VAR = `BLACK`;
[ID_VAR,DEVICE_VAR] = EXEC(`SELECT ID, DEVICE FROM
HARDWARE
WHERE COLOR = ?`,[
!!!RESOLVE EWI!!! /*** SSC-EWI-0053 - OBJECT COLOR_VAR MAY NOT WORK PROPERLY, ITS DATATYPE WAS NOT RECOGNIZED ***/!!!
COLOR_VAR]);
EXEC(`--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
CALL DBMS_OUTPUT.PUT_LINE_UDF(NVL(ID_VAR :: STRING, '') || ' ' || NVL(DEVICE_VAR :: STRING, ''))`);
$$;
For the following sample, EXEC call returns [12], with object destructuring ID_VAR stores 12:
[ID_VAR] = EXEC(`SELECT ID FROM PUBLIC.HARDWARE WHERE COLOR = 'BLACK'`);
The following two EXEC calls are alternative ways for the previous sample without object destructuring:
ID_VAR = EXEC(`SELECT ID FROM PUBLIC.HARDWARE WHERE COLOR = 'BLACK'`)[0];
ID_VAR = EXEC(`SELECT ID FROM PUBLIC.HARDWARE WHERE COLOR = 'BLACK'`, {vars:1});
Object destructuring also works with bindings as you may note on these statements (EXEC call returns [12, "MOUSE"] values):
COLOR_VAR = `BLACK`;
[ID_VAR,DEVICE_VAR] = EXEC(`SELECT ID, DEVICE FROM PUBLIC.HARDWARE WHERE COLOR = ?`,[COLOR_VAR]);
To obtain the actual result set returned by Snowflake, you can use this synaxis:
let RESULT_SET_COPY;
RESULT_SET_COPY = EXEC(`SELECT * FROM PUBLIC.HARDWARE WHERE COLOR = 'BLACK'`, {row:1});
/* RETURNS
{
"COLOR": "BLACK",
"DEVICE": "MOUSE",
"ID": 12,
"getColumnCount": {},
...
"next": {}
}*/
EXEC with record types
Oracle
IN -> Oracle_04.sql
--Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE EXECUTE_PROC AS
TYPE DEVTRECTYP IS RECORD (
ID NUMBER(4) NOT NULL := 0,
DEV_TYPE VARCHAR2(30) NOT NULL := 'UNKNOWN',
COLOR VARCHAR2(30) := 'GREEN'
);
DEV_VARIABLE DEVTRECTYP;
BEGIN
--STORES THE ROW VALUES IN THE RECORD
EXECUTE IMMEDIATE 'SELECT * FROM HARDWARE WHERE COLOR = ''BLACK''' INTO DEV_VARIABLE;
DBMS_OUTPUT.PUT_LINE(DEV_VARIABLE.ID || ' ' || DEV_VARIABLE.DEV_TYPE || ' ' || DEV_VARIABLE.COLOR);
END;
Snowflake
OUT -> Oracle_04.sql
CREATE OR REPLACE PROCEDURE EXECUTE_PROC ()
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.
class DEVTRECTYP {
ID = 0
DEV_TYPE = `UNKNOWN`
COLOR = `GREEN`
constructor() {
[...arguments].map((element,Index) => this[(Object.keys(this))[Index]] = element)
}
}
let DEV_VARIABLE = new DEVTRECTYP();
//STORES THE ROW VALUES IN THE RECORD
EXEC(`SELECT * FROM
HARDWARE
WHERE COLOR = 'BLACK'`,{
rec : DEV_VARIABLE
});
EXEC(`--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
CALL DBMS_OUTPUT.PUT_LINE_UDF(NVL(? :: STRING, '') || ' ' || NVL(? :: STRING, '') || ' ' || NVL(? :: STRING, ''))`,[DEV_VARIABLE.ID,DEV_VARIABLE.DEV_TYPE,DEV_VARIABLE.COLOR]);
$$;
This is still a work in progress. The transformation to properly store the record values will be:
EXEC(`SELECT * FROM PUBLIC.HARDWARE WHERE COLOR = 'BLACK'`, {rec:DEV_VARIABLE});
Used when you want to store the values returned by the query inside a record. Translation of records is described in . Record variable should be passed as an argument.