Node.js +Oracledb4.2+ executeMany + Error: NJS-011:遇到绑定值和类型错配

Question

我正在使用Node.js 12.16.1和NPMoraclev4.2、Oracle11g、Windows10机器，并试图使用executeMany命令插入大量数据，如下所示。

创建表

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代码

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

输出：

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

问题：

有人能引导我解决上述错误吗？我怀疑这是给SHIFTDATE的。提前谢谢你。

Answer 1

根据文档，https://oracle.github.io/node-oracledb/doc/api.html#oracledbconstantsnodbtype

问题是日期格式。然后，我必须使用Javascript日期(即new Date())，这已经在node-oracledb 4.2中得到了支持。

我传递的值如下所示，它起了作用。

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

Answer 2

最可能的问题是日期格式，它需要匹配会话日期格式NLS_DATE_FORMAT。

文档