nodejs oracledb select data WHERE IN values array

Question

I am trying to get rows where id in 1,2,3.

Here is my code:

app.use('/', async function(req, res, next){
    try{
        var query = await connection.execute(`SELECT * FROM ERROR_LIST WHERE ID IN :1`, [[1,2,3]]);
        console.log(query);
        res.send(query.rows)
    } catch(err) {
        next(err);
    }
   next();
});

As the result: ORA-01484: arrays can only be bound to PL/SQL statements

score 2 · Answer 1 · answered May 01 '20 at 23:07

2

The node-oracledb documentation has a whole section Binding Multiple Values to a SQL WHERE IN Clause.

You'll need one of the solutions, the simplest of which is like:

const sql = `SELECT last_name FROM employees WHERE first_name IN (:bv1, :bv2, :bv3, :bv4)`;
const binds = ['Alyssa', 'Christopher', 'Hazel', 'Samuel'];
const result = await connection.execute(sql, binds);

answered May 01 '20 at 23:07

Christopher Jones

9,449
3
24
48

1

what if you do not know how many elements come inside `binds` array? – Beki Mar 03 '22 at 01:39
2

The documentation link in the answer talks about that and gives solutions. – Christopher Jones Mar 03 '22 at 06:46

nodejs oracledb select data WHERE IN values array

1 Answers1