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

Question

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);
        });
    });
}

Mz A · Accepted Answer · 2018-07-20 07:50:56Z

1

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)

answered Jul 20, 2018 at 7:50

Mz A

1,11912 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aleksi Over a year ago

Here's a simpler example:

sql const names = ["name1", "name2", "name3"]; connection.execute(`WHERE TABLE.NAME IN (${names.map((name, index) => `:${index}`).join(", ")})`,         names)

source

drdv Over a year ago

another way i found succesful, even though could look awkward : const names = ["name1", "name2", "name3"]; connection.execute(`WHERE TABLE.NAME IN (${names.map((name) => "'" + name + "'"})`)

cbeeson · Accepted Answer · 2016-05-06 17:04:05Z

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.

Christopher Jones · Accepted Answer · 2016-01-22 22:26:15Z

0

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

answered Jan 22, 2016 at 22:26

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

1 Comment

sha35868 Over a year ago

Thanks Chris for the reference. The issue is clear to me now.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related