CODEWITHSUNDEEP
javascriptsqlnode.jsoracle-database
sha35868
sha35868

Reputation: 159

Oracle Node js - Javascript - Bind variable for IN clause in SQL statement

I am using Oracle's Node js driver. I know how to bind simple variables in an SQL statement but how do you bind a variable that uses an IN clause?

In the example below, my bind variable is :grp_ids and I want to bind it to an array of strings. But the code does not produce the expected result.

function test2()
{
  oracledb.getConnection(connInfo,
    function(err, connection)
    {
      if (err) {console.error(err.message); return; }
      var grpIds = '(\'0021\', \'1684\')';
      console.log(grpIds);
      connection.execute(`
        SELECT ag.grp_id, ag.grp_nm from acct_group ag
        WHERE ag.grp_id in :grp_ids`,
        {grp_ids: grpIds},
        function(err, result)
        {
          if (err) {console.error(err.message); return; }
          console.log(result.rows);
        });
    });
}

Upvotes: 2

Views: 4232

Answers (3)

Mz A
Mz A

Reputation: 1079

You can do it like this, I don't know if it gives you the performance benefit of 'bound variables' if your in clause list, size changes. Basically you have dynamically build up your sql string. 

const boundVars = someParamArray.map((val, index) => ':param'+index);
const boundVarsInClause = boundVars.join(',');
const boundParams = {};
boundVars.forEach((boundVar, index) => boundParams[boundVar.substr(1)] = req.platformKeys[index].vNumber);

const myQuery = `select * from some_table where some_col in ${boundVars}`
oracleThingMachingy.execute(myQuery, boundParams)

Upvotes: 1

cbeeson
cbeeson

Reputation: 76

You can't directly bind variables as you have pointed out however setting the cursor_sharing to force increases the extent to which sql statements can be shared and should reduce the number of loads required.

try executing:

ALTER session SET cursor_sharing=force

in your session before running the queries. So long as the number of items in your 'IN CLAUSE' list is the same then subsequent loads won't be needed.

You can track the number of times statements are being loaded and executed with v$sql:

select sql_text,
       loads,
       executions
from v$sql order by last_active_time desc ;

This blog is helpful for understanding what you are actually doing when changing the level of cursor sharing. I'd recommend ensuring you set cursor_sharing back to it's default of EXACT immediately afterwards, before executing further sql to keep FORCE to as minimal a scope as possible - it can change how more complex sql statements are processed.

Upvotes: 0

Christopher Jones
Christopher Jones

Reputation: 10536

You can't bind an array of values in an IN clause to Oracle DB since a bind value is treated as a single data unit. It is not treated as code text that can be parsed for comma separated values.

You might find this useful "Binding Multiple Values in an IN Clause" p169 in http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html

Upvotes: 0

Related Questions