How to use column.nextval with oracledb

Question

I have a column in my table for an ID that is normally populated with .nextval. The .nextval works perfectly as a normal sql statement such as INSERT INTO MYTABLE (COLA, COLB, COLC) VALUES (COLA_SEQ.nextval, 'ABCD', '25-JUL-2019'); Where COLA is a number, COLB is a varchar, and COLC is a date field. In node I am attempting to insert into the database as follows:

const oracledb = require('oracledb');
const async = require('async');

// Database connect function
const doconnect = function (cb) {
    oracledb.getConnection(database, cb);
};

// Database disconnect function
const dorelease = function (conn) {
    conn.close(function (err) {
        if (err)
            console.error(err.message);
    });
};

// Database insert function
const doinsert = function (conn, cb) {
    let table = "MYTABLE";
    let inserts = [{
        COLA: 'COLA_SEQ.nextval',
        COLB: 'ABCD',
        COLC: '25-JUL-2019'
    }];

    // Extract object keys from inserts and format them for Oracle
    // Oracle format needed => (COLUMN_A, COLUMN_A, COLUMN_C)
    let keys = Object.keys(inserts[0]);
    keys = '(' + keys.join(', ') + ')';

    // Extract object keys from inserts as value placeholders and format them for Oracle
    // Oracle format needed => (:COLUMN_A, :COLUMN_A, :COLUMN_C)
    let vals = Object.keys(inserts[0]);
    vals.forEach(function (element, idx) {
        vals[idx] = ':' + element;
    });
    vals = '(' + vals.join(', ') + ')';

    // Build sql insert statement
    // Oracle format needed => "INSERT INTO TABLE_A (COLUMN_A, COLUMN_A, COLUMN_C) VALUES (:COLUMN_A, :COLUMN_A, :COLUMN_C)"
    var sql = "INSERT INTO " + table + " " + keys + " VALUES " + vals;

    var options = {
        autoCommit: false,
        batchErrors: true
    };

    // Insert all data into Oracle database
    conn.executeMany(sql, inserts, options, function (err, result) {
        if (err)
            return cb(err, conn);
        else {
            // Commit all changes made
            conn.commit();

            return cb(null, conn);
        }
    });
};

// Async waterfall for sequential code exection (connect > query > disconnect)
async.waterfall(
    [
        doconnect,
        doinsert
    ],
    function (err, conn) {
        if (err) {
            console.error("In waterfall error cb: ==>", err, "<==");
            response.status = String(err);
        }
        if (conn)
            dorelease(conn);
    });

The error Oracle returns is

"Error: ORA-01722: invalid number"

which makes sense because .nextval is being passes as a string to a field that is a number. I can't seem to figure out how to pass the statement to oracle without it being read as a string.

Answer 1

Your code will produce a query:

INSERT INTO TABLE_A (COLUMN_A, COLUMN_B, COLUMN_C) VALUES (:COLUMN_A, :COLUMN_B, :COLUMN_C)

and then will try to assign the value 'COLA_SEQ.nextval' to the first bind variable. This value is a String it is not a Number (which is the data type expected for the column) and it is not a request to use a sequence.

If you want to use a sequence then you need to generate a query that is:

INSERT INTO TABLE_A (COLUMN_A, COLUMN_B, COLUMN_C) VALUES ( COLA_SEQ.nextval, :COLUMN_B, :COLUMN_C)

That does not use a bind variable but has the sequence in the statement.