EXEC Helper

EXEC function is a helper used to execute dynamic SQL inside a procedure.

You might also be interested in:

EXEC helper depends on IS NULL helper.

Syntax

EXEC(stmt) EXEC(stmt, binds[]) EXEC(stmt, opts{}) EXEC(stmt, binds[], opts{})

Parameters

stmt

The string of the SQL statement to execute.

binds (optional)

An array with the values or the variables to bind into the SQL statement.

opts (optional)

This is a Javascript object to describe how the values returned by the exec should be formated, this is used for SELECT statements.

Valid arguments for opts parameter

The following tables describe, how arguments should be sent to opts parameter in EXEC call:

Options when a query returns a single row

opts

description

{ }

When opts is empty or not sent to exec call, the data will be returned inside an array.

{vars: 0}

This has the same effect as the default option. It will return the data inside an array.

{vars: 1}

This is used when a query returns just one column and one row. EXEC will return the value directly. This is equivalent to EXEC(stmt)[0]

{rec:recordVariable}

Used when you want to store the values returned by the query inside a record. Translation of records is described in Records translation reference. Record variable should be passed as an argument.

{row: 1}

This option returns a copy of ResultSet, this means that the object returned contains the methods described in ResultSet Snowflake documentation.

Options when a query returns multiple rows

opts

Description

{row:2}

With this option, it always returns a copy of the ResultSet regardless of the number of rows returned by the EXEC.

General options

opts

Description

{sql:0}

It makes sure that the SQL implicit Cursor attribute is not modified after executing the statement.

EXEC Helper Function Definition 

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

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

CREATE OR REPLACE PROCEDURE PUBLIC.EXECUTE_PROC ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   
   //CREATES HARDWARE TABLE WITH COLUMNS ID, DEVICE AND COLOR
   //THIS IS AN EXECUTE IMMEDIATE JUST WITH AN STATEMENT
   EXEC(`CREATE OR REPLACE TABLE PUBLIC.HARDWARE ( ID NUMBER (38,19),
DEVICE VARCHAR(15),
COLOR VARCHAR(15))`);
$$;

EXEC with bindings

Oracle

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

CREATE OR REPLACE PROCEDURE PUBLIC.EXECUTE_PROC ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code  
    
   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 PUBLIC.HARDWARE VALUES (12, ?, 'BLACK')`,[`MOUSE`]);
   
   //INSERTS A ROW WITH  | 13 | KEYBOARD | WHITE |  VALUES USING DIRECT BINDING FOR 13 AND KEYBOARD
   EXEC(`INSERT INTO PUBLIC.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 PUBLIC.HARDWARE VALUES (?, ?, ?)`,[ID_VAR,DEVICE_VAR,COLOR_VAR]);
$$;

EXEC with options

Oracle

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

CREATE OR REPLACE PROCEDURE PUBLIC.EXECUTE_PROC ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   
    //STORES THE ID INTO ID_VAR
   [ID_VAR] = EXEC(`SELECT ID FROM PUBLIC.HARDWARE WHERE COLOR = 'BLACK'`);
   /* ** MSC-ERROR - MSCEWI3076 - TRANSLATION FOR BUILT-IN PACKAGE IS NOT CURRENTLY SUPPORTED. ** */
   /*   DBMS_OUTPUT.PUT_LINE(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 PUBLIC.HARDWARE WHERE COLOR = ?`,[COLOR_VAR]);
   /* ** MSC-ERROR - MSCEWI3076 - TRANSLATION FOR BUILT-IN PACKAGE IS NOT CURRENTLY SUPPORTED. ** */
   /*   DBMS_OUTPUT.PUT_LINE(ID_VAR || ' ' || DEVICE_VAR) */
   ;
$$;

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

You might be interested in Records transformation.

Oracle

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

CREATE OR REPLACE PROCEDURE PUBLIC.EXECUTE_PROC ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   
   class DEVTRECTYP {
      ID = 0
      DEV_TYPE = `UNKNOWN`
      COLOR = `GREEN`
   }
   let DEV_VARIABLE = new DEVTRECTYP();
   
   //STORES THE ROW VALUES IN THE RECORD
   [DEV_VARIABLE] = EXEC(`SELECT * FROM PUBLIC.HARDWARE WHERE COLOR = 'BLACK'`);
   /* ** MSC-ERROR - MSCEWI3076 - TRANSLATION FOR BUILT-IN PACKAGE IS NOT CURRENTLY SUPPORTED. ** */
   /*   DBMS_OUTPUT.PUT_LINE(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});

PreviousHelpersNextCursor Helper

Last updated