Unable to insert using executeMany() into Oracle DB with Node.js

Question

I am trying to insert data into Oracle database via Node JS. I am receiving data from Rest API. I am getting below error while running the code -

Error: NJS-005: invalid value for parameter 2

Here is my JSON Data getting from API -

[{ "id": 6, "type": "LOOKUP_ID", "value": "A", "description": "Access Group A", "instime": "2016-06-30", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null },
 { "id": 5, "type": "LOOKUP_ID", "value": "B", "description": "Access group for B", "instime": "2016-03-07", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null }, 
 { "id": 7, "type": "LOOKUP_ID", "value": "C", "description": "Access Group for C", "instime": "2017-07-11", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null }, 
 { "id": 10, "type": "LOOKUP_ID", "value": "M", "description": "Access Group for M", "instime": "2018-02-28", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null }];

Here is my Node JS code -

'use strict';

const oracledb = require('oracledb');
const express = require('express');
var request = require("request")
const app = express();


app.get('/', (req, res) => {
    oracledb.getConnection(
        {
            user: 'uid',
            password: 'passwd',
            connectString:
                '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521)(SEND_BUF_SIZE=)(RECV_BUF_SIZE=))(LOAD_BALANCE=yes))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dbhost)))',
        },
        connExecute
    );

     function connExecute(err, connection) {
        if (err) {
            console.error(err.message);
            res.send(err.message);
            return;
        }
        var url = "http://localhost:8080/api/employees"

        request({
            url: url,
            json: true
        }, async function (error, response, body) {
            try {
                if (!error && response.statusCode === 200) {

                    const data = JSON.stringify(body);
                    const sql = `INSERT INTO TABLE 
                    (ID, TYPE, VALUE, DESCR, INS, UPD, DELE, USER_INS, USER_UPD, USER_DELE) 
                    VALUES 
                (:id, :type, :value, :description, :instime, :updtime, 
                    :deltime, :insuser, :upduser, :deluser )`;

                    const binds = data;

                    const options = {
                        autoCommit: true,
                        bindDefs: {
                            id: { type: oracledb.NUMBER },
                            type: { type: oracledb.STRING, maxSize: 50 },
                            value: { type: oracledb.STRING, maxSize: 100 },
                            description: { type: oracledb.STRING, maxSize: 200 },
                            instime: { type: oracledb.DATE },
                            updtime: { type: oracledb.DATE },
                            deltime: { type: oracledb.DATE },
                            insuser: { type: oracledb.STRING, maxSize: 255 },
                            upduser: { type: oracledb.STRING, maxSize: 255 },
                            deluser: { type: oracledb.STRING, maxSize: 255 }
                        }
                    };

                    const result = await connection.executeMany(sql, binds, options);

                    console.log(result.rowsAffected);
                }
                } catch (error) {
                    console.log(error.message);
                }
        })
    }

    function connRelease(connection) {
        connection.close(function (err) {
            if (err) {
                console.error(err.message);
            }
        });
    }
});

const PORT = process.env.PORT || 8000;
app.listen(PORT, () => {
    console.log(`App listening on port ${PORT}`);
    console.log('Press Ctrl+C to quit.');
});

module.exports = app;

Here is my Table Structure -

I am not able to figure out what wrong I am doing here. Need some help to fix the issue.

Answer 1

In addition to the incorrect stringify that was already pointed out, you are binding strings like "2016-06-30" as the oracledb.DATE type so you will get an error.

There are various ways around this, but if your date is coming from an external source it's probably easier to bind as oracledb.STRING and make sure the DB knows what date format to expect. Here's one working example:

'use strict';

process.env.NLS_LANG='.AL32UTF8';
process.env.NLS_DATE_FORMAT='YYYY-MM-DD';
process.env.ORA_SDTZ = 'UTC';

const oracledb = require('oracledb');
const config = require('./dbconfig.js');

async function run() {
  let connection;

  try {
    connection = await oracledb.getConnection(config);

    const sql = `INSERT INTO MTABLE 
                 (ID, TYPE, VALUE, DESCR, INS, UPD, DELE, USER_INS, USER_UPD, USER_DELE) 
                 VALUES 
                 (:id, :type, :value, :description, :instime, :updtime, :deltime, :insuser, :upduser, :deluser )`;

    const data = [
      { "id": 6, "type": "LOOKUP_ID", "value": "A", "description": "Access Group A", "instime": "2016-06-30", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null },
      { "id": 5, "type": "LOOKUP_ID", "value": "B", "description": "Access group for B", "instime": "2016-03-07", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null }, 
      { "id": 7, "type": "LOOKUP_ID", "value": "C", "description": "Access Group for C", "instime": "2017-07-11", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null }, 
      { "id": 10, "type": "LOOKUP_ID", "value": "M", "description": "Access Group for M", "instime": "2018-02-28", "updtime": null, "deltime": null, "insuser": "ADMIN", "upduser": null, "deluser": null }
    ];

    const options = {
      autoCommit: true,
      bindDefs: {
        id: { type: oracledb.NUMBER },
        type: { type: oracledb.STRING, maxSize: 50 },
        value: { type: oracledb.STRING, maxSize: 100 },
        description: { type: oracledb.STRING, maxSize: 200 },
        instime: { type: oracledb.STRING, maxSize: 10 },
        updtime: { type: oracledb.STRING, maxSize: 10 },
        deltime: { type: oracledb.STRING, maxSize: 10 },
        insuser: { type: oracledb.STRING, maxSize: 255 },
        upduser: { type: oracledb.STRING, maxSize: 255 },
        deluser: { type: oracledb.STRING, maxSize: 255 }
      }
    };

    let result = await connection.executeMany(sql, data, options);
    console.log(result);
  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
    await connection.close();
      } catch (err) {
    console.error(err);
      }
    }
  }
}

run();