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

# EXEC Helper

{% hint style="info" %}
**Important Notice: Migration of Documentation Website**

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

[Official Snowflake Snowconvert Documentation](https://docs.snowflake.com/en/migrations/snowconvert-docs/translation-references/oracle/README)

For any immediate assistance or if you encounter any issues, please contact our support team at [snowconvert-support@snowflake.com](mailto:snowconvert-support@snowflake.com).

Thank you for your understanding.
{% endhint %}

{% hint style="info" %}
You might also be interested in:

* [DDL - DML Statements.](../sql-statements)
* [Commit.](../../sql-language-elements#commit)
* [Execute Immediate.](../../sql-language-elements#execute-immediate)
{% endhint %}

{% hint style="info" %}
EXEC helper depends on [IS NULL helper](is-null-helper)[.](is-null-helper)
{% endhint %}

## 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](../../collections-and-records#records). 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](https://docs.snowflake.com/en/sql-reference/stored-procedures-api.html#object-resultset).  |

#### 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](implicit-cursor-attribute-helper) is not modified after executing the statement. |

## EXEC Helper Function Definition

```javascript
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

{% hint style="info" %}
For all the samples, SnowConvert helpers Code were removed. You can find them [here.]()
{% endhint %}

The following code examples illustrates how EXEC works.

### EXEC simple case

Oracle

{% code title="IN -> Oracle_01.sql" %}
```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;
```
{% endcode %}

Snowflake

{% code title="OUT -> Oracle_01.sql" %}
```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))`);
$$;
```
{% endcode %}

### EXEC with bindings

Oracle

{% code title="IN -> Oracle_02.sql" %}
```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;
```
{% endcode %}

Snowflake

{% code title="OUT -> Oracle_02.sql" %}
```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]);
$$;
```
{% endcode %}

### EXEC with options

Oracle

{% code title="IN -> Oracle_03.sql" %}
```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;
```
{% endcode %}

Snowflake

{% code title="OUT -> Oracle_03.sql" %}
```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, ''))`);
$$;
```
{% endcode %}

For the following sample, EXEC call returns \[12], with object destructuring `ID_VAR` stores 12:

```sql
[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:

```sql
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):

```sql
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:

```sql
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

{% hint style="info" %}
You might be interested in [Records transformation](../../collections-and-records#records).
{% endhint %}

Oracle

{% code title="IN -> Oracle_04.sql" %}
```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;
```
{% endcode %}

Snowflake

{% code title="OUT -> Oracle_04.sql" %}
```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]);
$$;
```
{% endcode %}

{% hint style="warning" %}
This is still a work in progress. The transformation to properly store the record values will be:

```sql
EXEC(`SELECT * FROM PUBLIC.HARDWARE WHERE COLOR = 'BLACK'`, {rec:DEV_VARIABLE});
```
{% endhint %}

## Known Issues <a href="#known-issues" id="known-issues"></a>

No issues were found.

## Related EWIs <a href="#related-ewis" id="related-ewis"></a>

1. [SSC-EWI-0053](../../../../general/technical-documentation/issues-and-troubleshooting/conversion-issues/general/ssc-ewi-0053): Object may not work.
2. [SSC-FDM-OR0035](../../../../general/technical-documentation/issues-and-troubleshooting/functional-difference/oracle/ssc-fdm-or0035): DBMS\_OUTPUT.PUTLINE check UDF implementation
