node-oracledb - Bulk insert using associative array

Question

Im using node-oracledb driver in my nodeJs application. I am having to do bulk inserts (upto 6000 rows) and the batch insert feature of simple-oracledb extension did not meet my needs. It is painfully slow for 6000 records. I came across this post and this doc from node-oracledb however and it seems like a promising way. It's just that im new to PL/SQL that Im not understanding how to do it.

So consider I have the following table:

CREATE TABLE MY_TABLE 
( "CID" NUMBER,
"EMPID" VARCHAR2(10 BYTE));

I have 3 records for bulk insertion. Here is my PL/SQL package:

CREATE OR REPLACE PACKAGE MY_PKG IS
  TYPE cidtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE empidtype IS TABLE OF VARCHAR2(10);
  PROCEDURE insertproc(cids IN cidtype, empids IN empidtype);
END;
/

CREATE OR REPLACE PACKAGE BODY MY_PKG IS

  PROCEDURE insertproc(cids IN cidtype, empids IN empidtype) IS
  BEGIN
    FORALL i IN INDICES OF cids
      INSERT INTO MY_TABLE (cid, empid) VALUES (cids(i), empids(i));
  END;

END;
/

My NodeJS code:

var stmt = `BEGIN MY_PKG.insertproc(:cids, :empids); END;`;
var params = {
    cids: {
        type: oracledb.NUMBER,
        dir: oracledb.BIND_IN,
        val: [100, 101, 102]
    },
    empids: {
        type: oracledb.STRING,
        dir: oracledb.BIND_IN,
        val: ['ab165634', 'df123456', 'cd456789']
    }
};

connection.execute(stmt,params,function (err) { . . . });

However this throws the following error:

ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'INSERTPROC'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n

Since the example only show how u can bind array for 1 column, I couldnt figure out how its done for multiple columns (the entire row). Any help would be greatly appreciated!!!

TYPE cidtype IS TABLE OF MY_TABLE.CID%TYPE INDEX BY BINARY_INTEGER; TYPE empidtype IS TABLE OF MY_TABLE.EMPID%TYPE INDEX BY BINARY_INTEGER; — MT0
– MT0, Commented Oct 26, 2017 at 23:39

ABHAY JOHRI · Accepted Answer · 2020-02-29 10:22:20Z

1

var sql = "insert into employee(id,name) values (:1,:2)";
var content = [];
for(i=0;i<data.length.i++)
{
var temp = [];
temp.push(data[i].id);
temp.push(data[i].name);
content.push(temp);
}

connection.executeMany(sql,content,function(err,result)
{
   if(err)
   console.log(err);
   else
   console.log("Success");
});

answered Feb 29, 2020 at 10:22

ABHAY JOHRI

2,1541 gold badge18 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christopher Jones Over a year ago

If you change the SQL to ... values (:id, :name), and your data array only has id and name` attributes, then you don't need to construct the content array - you can use connection.executeMany(sql, data, .... You may want to use let and const instead of the old var syntax. And also look at using async/await instead of callbacks. See node-oracledb examples.

Dan McGhan · Accepted Answer · 2017-10-27 01:14:17Z

0

I talk a bit about this here, but really I need to have more examples...

Your code will work if you add the "INDEX BY BINARY_INTEGER" to your empidtype type. That changes it from a nested table to an associative array, which is what the driver can currently bind to.

Here's a similar question I answered: How to insert multiple records into oracle db using node js

And another that demonstrates how to do this in batches (second example): Node.js + OracleDb - Insert the last date many times

The batching would be much easier now with async/await.

Finally (I promise), you might find these slide interesting: https://www.dropbox.com/s/69jt5uu1fqus84c/Tips%20and%20Tricks%20for%20Getting%20Started%20with%20the%20Oracle%20Database%20Driver%20for%20Node.pdf?dl=0

edited Oct 27, 2017 at 1:14

answered Oct 26, 2017 at 23:32

Dan McGhan

4,6691 gold badge14 silver badges15 bronze badges

Comments

Christopher Jones · Accepted Answer · 2018-04-02 22:42:19Z

0

Node-oracledb 2.2 introduces connection.executeMany() (also see Batch Statement Execution) making batch data insert more efficient.

answered Apr 2, 2018 at 22:42

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Collectives™ on Stack Overflow

node-oracledb - Bulk insert using associative array

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related