Node.js Oracledb executeMany() fail to update database

Question

Im am Using oracledb package for NodeJs to perform multiple update in a database by using executeMany(), unfortunately each time i try to update i get the following Error: ORA-01722: invalid number .

Data Types from Database: Data Types from Database

Sample data Existing in Data base Sample Data

Output Error: Error

Oracle database Version is 11g

Where am i doing it wrong.?? Help please.

Here is my Sample Code Snippet.

oracledb.getConnection(connAttrs_, function (err, connection) {
    if (err) {
        //Error connecting to DB
        res.set('Content-Type', 'application/json');
        res.status(500).send(JSON.stringify({
            status: 500,
            message: "Error connecting to DB",
            detailed_message: err.message
        }));
        console.log(err.message);
        return;
    }

    var sql = "UPDATE tblTestBulkUpdate SET NAME =:2 WHERE TO_NUMBER(ID) =:1";
    
    var binds = [
        [10, "updated"],
        [11, "updated two"],
        [20, "why this"],
        [22, "dummy data"],
        [30, "ok ok"],
        [40, "fig zero"],
        [45, "fig five"],
        [47, "fig seven"],

    ];

    var options = {
        autoCommit: true,  
        batchErrors: true, 
        bindDefs: [         
        
            { type: oracledb.NUMBER},
            { type: oracledb.STRING, maxSize: 50} 
            
        ]
    };
    
    connection.executeMany(sql, binds, options, function (err, result) {
        if (err)
          console.error(err);
        else {
          console.log("Result is:", result);
        }
      });
});

Answer 1

Using syntax like:

var binds = [
        [10, "updated"],
...

is 'bind by position', not 'bind by number', so the 10 maps to the first bind variable parsed in the statement which happens to be called ":2". See the doc which says

The position of the array values corresponds to the position of the SQL bind parameters as they occur in the statement, regardless of their names. This is still true even if the bind parameters are named like :0, :1, etc.

You can experiment with snippets like this, which will work because the binds variable has the string first:

sql = `select * from dual where 'abc' = :2 and 123 = :1`;
binds = ['abc', 123];
result = await connection.execute(sql, binds);

If you can't change the data order, then try using bind by name syntax.

There is an example of bind-by-name syntax in em_insert1.js:

const sql = "INSERT INTO no_em_tab values (:a, :b)";

const binds = [
  { a: 1, b: "Test 1 (One)" },
  { a: 2, b: "Test 2 (Two)" },
  { a: 3, b: "Test 3 (Three)" },
  { a: 4 },
  { a: 5, b: "Test 5 (Five)" }
];

// bindDefs is optional for IN binds but it is generally recommended.
// Without it the data must be scanned to find sizes and types.
const options = {
  autoCommit: true,
  bindDefs: {
    a: { type: oracledb.NUMBER },
    b: { type: oracledb.STRING, maxSize: 15 }
  }
};