8. Executing Batch Statements and Bulk Loading
The connection.executeMany()
method allows many
sets of data values to be bound to one DML or PL/SQL statement for
execution. It is like calling connection.execute()
multiple times but requires fewer round-trips. This is
an efficient way to handle batch changes, for example when doing bulk
inserts, or for updating multiple rows. The method cannot be used for
queries.
The executeMany()
method supports IN, IN OUT and OUT binds for most
data types except PL/SQL Collection Associative
Arrays.
There are runnable examples in the GitHub examples directory.
For example, to insert three records into the database:
const sql = `INSERT INTO mytab VALUES (:a, :b)`;
const binds = [
{ a: 1, b: "One" },
{ a: 2, b: "Two" },
{ a: 3, b: "Three" }
];
const options = {
autoCommit: true,
bindDefs: {
a: { type: oracledb.NUMBER },
b: { type: oracledb.STRING, maxSize: 5 }
}
};
const result = await connection.executeMany(sql, binds, options);
console.log(result.rowsAffected); // 3
Strings and Buffers require a maxSize
value in bindDefs
. It must
be the length (or greater) of the longest data value. For efficiency,
keep the size as small as possible.
The options parameter is optional.
If bindDefs is not set, then the bind
direction is assumed to be IN, and the bind data
are used to determine the bind variable types, names and maximum sizes.
Using bindDefs
is generally recommended because it removes the
overhead of scanning all records.
The bind definitions bindDefs
can also use “bind by position”
syntax, see the next examples.
Along with using executeMany()
, tune your data
loads by reviewing your
schema design and its triggers, sequences, indexes, partitioning, and
redo generation.
Attempting to use very large data sets may give the error DPI-1015:
array size is too large. To avoid this, repeatedly call
executeMany()
with subsets of the data.
8.1. Identifying Affected Rows with executeMany()
When executing a DML statement the number of database rows affected for each input record can be shown by setting dmlRowCounts. For example when deleting rows:
const sql = `DELETE FROM tab WHERE id = :1`;
const binds = [
[20],
[30],
[40]
];
const options = { dmlRowCounts: true };
const result = await connection.executeMany(sql, binds, options);
console.log(result.dmlRowCounts);
If the table originally contained three rows with id of 20, five rows with id of 30 and six rows with id of 40, then the output would be:
[ 3, 5, 6 ]
8.2. Handling Data Errors with executeMany()
With large sets of data, it can be helpful not to abort processing on the first data error, but to continue processing and resolve the errors later.
When batchErrors is true,
processing will continue even if there are data errors in some records.
The executeMany()
callback error parameter is not set. Instead, an
array containing each error will be returned in the callback result
parameter. All valid data records will be processed and a transaction
will be started but not committed, even if autoCommit
is true. The
application can examine the errors, take action, and explicitly commit
or rollback, as desired.
For example:
const sql = `INSERT INTO childtab VALUES (:1, :2, :3)`;
const binds = [
[1016, 10, "Child 2 of Parent A"],
[1017, 10, "Child 3 of Parent A"],
[1018, 20, "Child 4 of Parent B"],
[1018, 20, "Child 4 of Parent B"], // duplicate key
[1019, 30, "Child 3 of Parent C"],
[1020, 40, "Child 4 of Parent D"],
[1021, 75, "Child 1 of Parent F"], // parent does not exist
[1022, 40, "Child 6 of Parent D"]
];
const options = {
autoCommit: true,
batchErrors: true,
bindDefs: [
{ type: oracledb.NUMBER },
{ type: oracledb.NUMBER },
{ type: oracledb.STRING, maxSize: 20 }
]
};
const result = await connection.executeMany(sql, binds, options);
console.log(result.batchErrors);
The output is an array of error objects that were
reported during execution. The offset
property corresponds to the
0-based index of the executeMany()
binds
parameter array, indicating which record could
not be processed:
[ { Error: ORA-00001: unique constraint (HR.CHILDTAB_PK) violated errorNum: 1, offset: 3 },
{ Error: ORA-02291: integrity constraint (HR.CHILDTAB_FK) violated - parent key not found errorNum: 2291, offset: 6 } ]
Note that some classes of error will always return via the
executeMany()
callback error object, not as batch errors. No
transaction is created in this case. This includes errors where string
or buffer data is larger than the specified
maxSize value.
8.3. DML RETURNING with executeMany()
Values can be returned with DML RETURNING syntax:
const sql = `INSERT INTO tab VALUES (:1) RETURNING ROWID INTO :2`;
const binds = [
["One"],
["Two"],
["Three"]
];
const options = {
bindDefs: [
{ type: oracledb.STRING, maxSize: 5 },
{ type: oracledb.STRING, maxSize: 18, dir: oracledb.BIND_OUT },
]
};
const result = await connection.executeMany(sql, binds, options);
console.log(result.outBinds);
Output is:
[ [ [ 'AAAmI9AAMAAAAnVAAA' ] ],
[ [ 'AAAmI9AAMAAAAnVAAB' ] ],
[ [ 'AAAmI9AAMAAAAnVAAC' ] ] ]
8.4. Calling PL/SQL with executeMany()
The executeMany()
method can be used to execute a PL/SQL statement
multiple times with different input values. For example, the following
PL/SQL procedure:
CREATE PROCEDURE testproc (
a_num IN NUMBER,
a_outnum OUT NUMBER,
a_outstr OUT VARCHAR2)
AS
BEGIN
a_outnum := a_num * 2;
FOR i IN 1..a_num LOOP
a_outstr := a_outstr || 'X';
END LOOP;
END;
/
can be called like:
const sql = `BEGIN testproc(:1, :2, :3); END;`;
// IN binds
const binds = [
[1],
[2],
[3],
[4]
];
const options = {
bindDefs: [
{ type: oracledb.NUMBER },
{ type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
{ type: oracledb.STRING, dir: oracledb.BIND_OUT, maxSize: 20 }
]
};
const result = await connection.executeMany(sql, binds, options);
console.log(result.outBinds);
The returned bind values are:
[ [ 2, 'X' ],
[ 4, 'XX' ],
[ 6, 'XXX' ],
[ 8, 'XXXX' ] ]
The variant of executeMany()
that accepts a number of iterations is
useful when there are no bind values, or only OUT bind values. This
example calls a PL/SQL block eight times:
const plsql = `DECLARE
t_id NUMBER;
BEGIN
SELECT NVL(COUNT(*), 0) + 1 INTO t_id FROM testtable;
INSERT INTO testtable VALUES (t_id, 'Test String ' || t_id);
SELECT SUM(id) INTO :1 FROM testtable;
END;`
const options = {
bindDefs: [
{ type : oracledb.NUMBER, dir : oracledb.BIND_OUT }
]
};
const numIterations = 8;
const result = await connection.executeMany(plsql, numIterations, options);
console.log(result.outBinds);
Output would be an array of eight values such as:
[ [ 6 ], [ 10 ], [ 15 ], [ 21 ], [ 28 ], [ 36 ], [ 45 ], [ 55 ] ]
8.5. Binding Objects with executeMany()
You can use executeMany()
with Oracle Database
objects. For example, given a procedure myproc
that
accepts and returns a RECORD:
CREATE OR REPLACE PACKAGE rectest AS
TYPE rectype IS RECORD (name VARCHAR2(40), pos NUMBER);
PROCEDURE myproc (p_in IN rectype, p_out OUT rectype);
END rectest;
/
This can be called like:
const RectypeClass = await connection.getDbObjectClass("RECTEST.RECTYPE");
const plsql = `CALL rectest.myproc(:inbv, :outbv)`;
// Input data
binds = [
{ inbv: { NAME: 'Car', POS: 56 } },
{ inbv: { NAME: 'Train', POS: 78 } },
{ inbv: { NAME: 'Bike', POS: 83 } }
];
options = {
bindDefs: {
inbv: { type: RectypeClass },
outbv: { type: RectypeClass, dir: oracledb.BIND_OUT },
}
};
result = await connection.executeMany(plsql, binds, options);
for (const b of result.outBinds) {
console.log(b.outbv);
}
Each value to be bound to inbv
is a record’s data. The attribute
names correspond to the attributes of the PL/SQL record type using
Oracle Database’s standard casing rules. Since rectype
was created
with case insensitive names, these are represented as uppercase
attribute names in the JavaScript objects
See examples/plsqlrecord.js for a runnable sample.