Node.js + Oracledb 4.2 + executeMany + Error: NJS-011: encountered bind value and type mismatch

Question

I am using Node.js 12.16.1 and npm oracle v4.2, Oracle 11g, Windows 10 machine and trying to insert bulk data using executeMany command as below.

CREATE TABLE

CREATE TABLE DOWNTIME_HOURLY
(
  SHIFTDAY NUMBER NOT NULL,
  MACHINENAME VARCHAR2(20) NOT NULL,
  PLANID VARCHAR2(20) NOT NULL,
  PARTNAME VARCHAR2(20) NOT NULL,
  CATEGORY VARCHAR2(20) NOT NULL,
  SHIFTDATE DATE NOT NULL
);

NODE.js CODE

const oracledb = require('oracledb');
const credentials = { user: 'asdasdasd', password: 'asdasdasd!23', connectionString: 'hostname/ORCL' };

const options = {
    autoCommit: true,
    bindDefs: {
        "SHIFTDAY": { type: oracledb.NUMBER },
        "MACHINENAME": { type: oracledb.STRING, maxSize: 20 },
        "PLANID": { type: oracledb.STRING, maxSize: 20 },
        "PARTNAME": { type: oracledb.STRING, maxSize: 20 },
        "CATEGORY": { type: oracledb.STRING, maxSize: 20 },
        "SHIFTDATE": { type: oracledb.DATE }
    }
  };

const bindings = [
    {
        "SHIFTDAY": 12,
        "MACHINENAME": "test",
        "PLANID": "test",
        "PARTNAME": "test",
        "CATEGORY": "test",
        "SHIFTDATE": "03-APR-20"
    }];

const sql = `INSERT INTO DOWNTIME_HOURLY 
            (SHIFTDAY,MACHINENAME,PLANID,PARTNAME,CATEGORY,SHIFTDATE) 
             VALUES 
            (:SHIFTDAY, :MACHINENAME, :PLANID, :PARTNAME, :CATEGORY, :SHIFTDATE)`

const insert = async (credentials) => {
    const conn = await oracledb.getConnection(credentials).catch(err => console.log('ERRRRR....', err));
    console.log('Connection successful');
    oracledb.fetchAsString = [oracledb.DATE];
    let result = await conn.execute(`SELECT current_date, current_timestamp FROM DUAL`);
    console.log(result);
    result = await conn.executeMany(sql, bindings, options).catch(err => console.log('Execution ERRRRR....', err));
    console.log('Query executed: ' , result);
}

insert(credentials);

OUTPUT:

PS D:\project> node try
Connection successful

{
  metaData: [ { name: 'CURRENT_DATE' }, { name: 'CURRENT_TIMESTAMP' } ],
  rows: [ [ '03-APR-20', '03-APR-20 10.43.09.643015 AM UTC' ] ]
}

Execution ERR.... Error: NJS-011: encountered bind value and type mismatch
    at Connection.executeMany (D:\project\node_modules\oracledb\lib\connection.js:203:21)
    at D:\project\node_modules\oracledb\lib\util.js:202:16
    at new Promise (<anonymous>)
    at Connection.executeMany (D:\project\node_modules\oracledb\lib\util.js:190:14)
    at insert (D:\project\try.js:36:25)

Query executed:  undefined

QUESTION:

Can somebody guide me to resolution for above error. I suspect this is for SHIFTDATE. Thank you so much in advance.

Answer 1

As Per documentation, https://oracle.github.io/node-oracledb/doc/api.html#oracledbconstantsnodbtype

The problem was the date format. I then have to use the Javascript date (i.e. new Date()) which is already supported in node-oracledb 4.2.

I passed values as below and it worked.

const bindings = [
    {
        "SHIFTDAY": 12,
        "MACHINENAME": "test",
        "PLANID": "test",
        "PARTNAME": "test",
        "CATEGORY": "test",
        "SHIFTDATE": new Date()
    }];