SnowConvert - Translation Spec
  • For Teradata
  • Translation Reference
    • Teradata
      • Scripts to Snowflake SQL Translation Reference
        • COMMON STATEMENTS
          • ERROR HANDLING
          • EXIT or QUIT
          • GOTO
          • IF... THEN...
        • BTEQ
        • MLOAD
          • Import
      • Scripts To Python Translation Reference
        • BTEQ
          • REPEAT
          • USING REQUEST MODIFIER
        • FLOAD
          • BEGIN LOADING
        • MLOAD
          • BEGIN MLOAD
        • TPT
        • SnowConvert Scripts Helpers
          • Technical Documentation
      • SQL Translation Reference
        • Built-in Functions
          • CURRENT_TIMESTAMP
          • Cast to DATE using { }
          • Cast to INTERVAL datatype
          • COALESCE
          • DAYNUMBER_OF_MONTH
          • FROM_BYTES
          • GETQUERYBANDVALUE
          • JSON_CHECK
          • JSON_EXTRACT
          • JSON_TABLE
          • NEW JSON
          • NVP
          • OVERLAPS
          • PIVOT
          • P_INTERSECT
          • RANK
          • Regex functions
          • STRTOK_SPLIT_TO_TABLE
          • SUBSTRING
          • TD_UNPIVOT
          • TO_CHAR
          • XMLAGG
        • Database DBC
        • Data Types
        • DDL
          • Tables
            • WITH DEFAULT
          • Index
          • Views
        • DML
          • Delete Statement
          • Insert Statement
            • LOGGING ERRORS
          • Select Statement
            • Expand On Clause
            • Normalize
            • Reset When
          • Set Operators
          • Update Statement
          • With Modifier
        • SnowConvert Procedures Helpers
          • Cursor Helper
          • Exec Helper
          • Functional Equivalence Helpers
          • Into Helper
      • Teradata to JavaScript Translation Reference
        • GET DIAGNOSTICS EXCEPTION
        • Macros
        • Procedures
      • Teradata to Snowflake Scripting Translation Reference
        • ABORT and ROLLBACK
        • BEGIN END
        • CASE
        • CREATE MACRO
        • CREATE PROCEDURE
        • CURSOR
        • DECLARE
        • DECLARE CONDITION HANDLER
        • DML and DDL Objects
        • EXCEPTION HANDLERS
        • EXECUTE IMMEDIATE
        • EXECUTE/EXEC
        • FUNCTION OPTIONS OR DATA ACCESS
        • GET DIAGNOSTICS EXCEPTION
        • IF
        • LOCKING FOR ACCESS
        • LOOP
        • OUTPUT PARAMETERS
        • PREPARE
        • REPEAT
        • SET
        • SYSTEM_DEFINED
        • WHILE
    • Oracle
      • Sample data
      • Basic Elements of Oracle SQL
        • Data Types
          • Oracle Built-in Data Types
            • Character Data Types
              • CHAR Data type
              • NCHAR Data Type
              • VARCHAR2 Data Type
              • VARCHAR Data Type
              • NVARCHAR2 Data Type
            • Numeric Data Types
              • NUMBER Data Type
              • FLOAT Data Type
              • Floating-Point Numbers
                • BINARY_FLOAT
                • BINARY_DOUBLE
            • LONG Data Type
            • Datetime and Interval Data Types
              • DATE Data Type
              • TIMESTAMP Data Type
              • TIMESTAMP WITH TIME ZONE Data Type
              • TIMESTAMP WITH LOCAL TIME ZONE Data Type
              • INTERVAL YEAR TO MONTH Data Type
              • INTERVAL DAY TO SECOND Data Type
              • Datetime Arithmetic
                • Interval UDFs vs Snowflake native interval operation
            • LOB Data Types
              • BFILE Data Type
              • BLOB Data Type
              • CLOB Data Type
              • NCLOB Data type
            • JSON Data Type
            • Extended Data Types
            • RAW and LONG RAW Data types
            • PL SQL Data Types
              • PLS_INTEGER Data Type
              • BINARY_INTEGER Data Type
          • Rowid Data Type
            • ROWID DataType
            • UROWID Data Type
          • ANSI Data Types
          • User-Defined Types
            • REF Data Types
          • Any Types
            • ANYTYPE
            • ANYDATA
            • ANYDATASET
          • XML Types
            • XMLType
            • URI Data Types
              • HTTPURIType
              • XDBURIType
              • DBURIType
            • URIFactory Package
          • Spatial Types
            • SDO_GEOMETRY
            • SDO_TOPO_GEOMETRY
            • SDO_GEORASTER
        • Literals
          • Interval Literal
          • Interval Type and Date Type
          • Text literals
      • Pseudocolumns
        • ROWID
        • ROWNUM
      • Built-in functions
        • SnowConvert Custom UDFs
          • BFILENAME UDF
          • DATE_TO_JULIANDAYS_UDF
          • DATEADD UDF
          • DATEDIFF UDF
          • INTERVAL UDFs
            • DATEADD UDF INTERVAL
            • DATEDIFF UDF INTERVAL
          • CAST_DATE UDF
          • JSON_VALUE UDF
          • JULIAN TO GREGORIAN DATE UDF
          • MONTHS BETWEEN UDF [DEPRECATED]
          • REGEXP LIKE UDF
          • TIMESTAMP DIFF UDF
          • TRUNC (date) UDF
          • TRUNC (number) UDF
        • TO_NUMBER
        • NLSSORT
      • Built-In packages
        • DBMS_LOB
          • SUBSTR Function
        • DBMS_RANDOM
          • VALUE functions
        • DBMS_OUTPUT
          • PUT_LINE procedure
        • UTL_FILE
          • FOPEN procedure
          • PUT_LINE procedure
          • FCLOSE procedure
      • SQL Queries and Subqueries
        • Select
          • Select Flashback Query
        • Joins
          • Equijoin
          • Band Join
          • Self Join
          • Cartesian Products
          • Inner Join
          • Outer Join
          • Antijoin
          • Semijoin
      • SQL Statements
        • Alter Session
        • Alter Table
        • Create Materialized Views
        • Create Database Link
        • Create Index
        • Create Sequence
        • Create Synonym
        • Create Table
        • Create Type
          • Object Type Definition
          • Subtype Definition
          • Array Type Definition
          • Nested Table Type Definition
          • Member Function Definitions
        • Create View
        • Drop Table
      • PL/SQL to Snowflake Scripting
        • ASSIGNMENT STATEMENT
        • CALL
        • CASE
        • COLLECTIONS AND RECORDS
          • Associative Array Type Definition
          • Varray Type Definition
          • Nested Table Array Type Definition
          • Collection Methods
          • Collection Bulk Operations
            • WITH, SELECT, and BULK COLLECT INTO statements
          • Record Type Definition
        • COMPOUND STATEMENTS
        • CONTINUE
        • CREATE PROCEDURE
        • CURSOR
          • PARAMETRIZED CURSOR
          • CURSOR DECLARATION
          • Workaround for cursors using parameters or procedure variables
          • Cursor Variables
        • DECLARE
        • DEFAULT PARAMETERS
        • DML STATEMENTS
          • INSERT Statement Extension
          • MERGE Statement
          • SELECT INTO Statement
          • Work around to simulate the use of Records
        • EXIT
        • EXPRESSIONS
        • EXECUTE IMMEDIATE
        • FORALL
        • FOR LOOP
        • HELPERS
          • Bulk Cursor Helpers
        • IF
        • IS EMPTY
        • LOCK TABLE
        • LOG ERROR
        • LOOP
        • OUTPUT PARAMETERS
        • PACKAGES
          • DECLARATION
          • BODY
          • VARIABLES
          • Constants
        • PROCEDURE CALL
        • RAISE
        • RAISE_APPICATION_ERROR
        • UDF CALL
        • WHILE
      • PL/SQL to Javascript
        • Procedures
        • User defined functions
        • Packages
        • Helpers
          • EXEC Helper
          • Cursor Helper
          • Raise Helper
          • ROWTYPE Helper
          • Between operator helper
          • Like operator Helper
          • IS NULL Helper
          • Concat Value Helper
          • Package variables helper
          • Implicit Cursor attribute helper
        • Declarations
        • Control Statements
        • Conditional Compilation
        • Collections & Records
        • DDL - DML Statements
        • SQL Language Elements
        • Expressions and operators
        • Synonyms
        • Triggers
        • TYPE attribute
      • SQL*Plus
        • Archive Log
        • Attribute
        • Break
        • Btitle
        • Change
        • Column
        • Define
        • Host
        • Prompt
        • Remark
        • Set
        • Spool
        • Start
        • Whenever oserror
        • Whenever sqlerror
        • Show
        • Append
        • Accept
      • Wrapped objects
    • SQLServer
      • General Language Elements
        • COMPUTED COLUMN
        • EXECUTE
          • System Store Procedures
            • SP_RENAME
        • Collate
        • USE
        • OUTER APPLY
      • DDLs
        • Tables
        • Index
        • Views
        • Procedures
        • Functions
      • DMLs
        • Set Operators
        • Between
        • Update
        • Select
        • Insert
        • Delete
        • Merge
        • Exists
        • IN
        • Truncate
        • Drop
        • Bulk Insert
        • Common Table Expression (CTE)
        • Drops
      • Data Types
      • Statements
        • ALTER
          • TABLE
            • ADD
              • COLUMN DEFINITION
                • COLUMN CONSTRAINT
                  • FOREIGN KEY
                  • PRIMARY KEY / UNIQUE
                  • CHECK
              • TABLE CONSTRAINT
                • FOREIGN KEY
                • PRIMARY KEY
                • CHECK CONSTRAINT
                • CHECK
                • CONNECTION
                • DEFAULT
                • ON PARTITION
        • CREATE
          • FUNCTION
            • SCALAR
            • INLINE TABLE-VALUED
            • MULTI-STATEMENT TABLE-VALUED
      • Built-in functions
        • SnowConvert custom UDFs
          • OPENXML UDF
          • STR UDF
          • SWITCHOFFSET_UDF
        • Aggregate functions
          • COUNT
          • COUNT_BIG
          • SUM
        • Analytic Functions
          • LAG
        • Conversion functions
          • CONVERT
          • TRY_CONVERT
        • Data Type functions
          • DATALENGTH
        • Date & Time functions
          • AT TIME ZONE
          • SWITCHOFFSET
          • DATEADD
          • DATEDIFF
          • DATEPART
          • DATEFROMPARTS
          • DATENAME
          • DAY
          • EOMONTH
          • GETDATE
          • MONTH
          • SYSDATETIME
          • SYSUTCDATETIME
          • YEAR
        • Logical functions
          • IIF
        • Mathematical functions
          • ABS
          • ACOS
            • ACOS in JS
          • ASIN
            • ASIN in JS
          • ATAN
            • ATAN in JS
          • ATN2
            • ATAN2 in JS
          • AVG
          • CEILING
          • COS
            • COS in JS
          • COT
            • COT in JS
          • DEGREES
            • DEGREES in JS
          • EXP
            • EXP in JS
          • FLOOR
          • LOG
            • LOG in JS
          • LOG10
            • LOG10 in JS
          • PI
            • PI in JS
          • POWER
            • POW in JS
          • SQUARE
          • STDEV
          • STDEVP
          • VAR
          • POWER
          • RADIANS
            • RADIANS in JS
          • ROUND
          • SQRT
        • Metadata functions
          • DB_NAME
          • OBJECT_ID
        • Ranking functions
          • DENSE_RANK
          • RANK
          • ROW_NUMBER
        • String functions
          • ASCII
            • ASCII in JS
          • CHAR
          • CHARINDEX
          • COALESCE
          • CONCAT
          • CONCAT_WS
            • Join in JS
          • DIFFERENCE
            • DIFFERENCE in JS
          • FORMAT
            • FORMAT in JS
          • LEFT
          • LEN
          • LOWER
          • LTRIM
            • LTRIM in JS
          • NCHAR
          • PATINDEX
            • search in JS
          • QUOTENAME
            • QUOTENAME in JS
          • REPLACE
          • REPLICATE
          • REVERSE
            • reverse in JS
          • RIGHT
          • RTRIM
          • SOUNDEX
            • SOUNDEX in JS
          • SPACE
          • STR
            • STR in JS
          • STRING_ESCAPE
            • stringify in JS
          • SUBSTRING
          • TRIM
            • trim in JS
          • UPPER
        • System functions
          • FORMATMESSAGE
            • FORMATMESSAGE_UDF
          • ISNULL
          • NEWID
          • NULLIF
        • XML Functions
          • Value
          • Query
      • Built-in procedures
        • Custom User Defined Procedures
          • SP_ADDEXTENDEDPROPERTY_UDP
      • Snowflake Scripting
        • CREATE PROCEDURE
        • CASE
        • CURSOR
        • DECLARE
        • EXECUTE
        • IF
        • SET
        • DMLs
        • CALL
        • WHILE
        • DROPs
        • BEGIN and COMMIT Transaction
        • OUTPUT PARAMETERS
        • LABEL and GOTO
        • SELECT
        • TEXTIMAGE_ON
        • TRY CATCH
      • System Tables
        • SYS.FOREIGN_KEYS
      • Queries
        • TOP
Powered by GitBook
On this page
  • Syntax
  • Parameters
  • stmt
  • binds (optional)
  • opts (optional)
  • EXEC Helper Function Definition
  • Usage Samples
  • EXEC simple case
  • EXEC with bindings
  • EXEC with options
  • EXEC with record types
  • EWIs Related
  1. Translation Reference
  2. Oracle
  3. PL/SQL to Javascript
  4. Helpers

EXEC Helper

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

PreviousHelpersNextCursor Helper

Last updated 1 year ago

You might also be interested in:

EXEC helper depends on

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}

{row: 1}

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}

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

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(`!!!RESOLVE EWI!!! /*** SSC-EWI-OR0115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. ***/!!!
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(`!!!RESOLVE EWI!!! /*** SSC-EWI-OR0115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. ***/!!!
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(`!!!RESOLVE EWI!!! /*** SSC-EWI-OR0115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. ***/!!!
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});

EWIs Related

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.

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

It makes sure that the is not modified after executing the statement.

For all the samples, SnowConvert helpers Code were removed. You can find them

You might be interested in .

: CUSTOM UDF INSERTED.

: Object may not work.

here.
ResultSet Snowflake documentation
SQL implicit Cursor attribute
DDL - DML Statements.
IS NULL helper
.
Records transformation
Records translation reference
Commit.
Execute Immediate.
SCC-EWI-0020
SSC-EWI-0053