node-oracledb
node-oracledb copied to clipboard
oracle
executeMany() offsets index of dbl returning variables on index
- What versions are you using?
platform: darwin
version: v20.11.1
arch: x64
oracledb: 5.5.0 (reproducible in latest 6.x)
clientVersion: 19.3.0.0.0
- Is it an error or a hang or a crash?
Error
- What error(s) or behavior you are seeing?
When encountering batchErrors, the index / values of dbms returning can become offset incorrectly
// results.batchErrors
[
[Error: ORA-12899: value too large for column "REPRODUCTION_CONTRACT_TABLE"."CURRENCY_CODE" (actual: 46, maximum: 20)] {
errorNum: 12899,
offset: 1
}
]
// results.outBidns
[
{
contractLineIdOut: [ 10000000050 ],
contractIdOut: [ 100 ],
creationDateOut: [ 2021-01-01T05:00:00.000Z ],
lineItemOut: [ 'Line Item 1' ]
},
{
contractLineIdOut: [],
contractIdOut: [],
creationDateOut: [],
lineItemOut: []
},
{
contractLineIdOut: [ 4.089930721500069e-28 ],
contractIdOut: [ 10000000059 ],
creationDateOut: [ 9309-01-01T12:00:00.000Z ],
lineItemOut: [ 'xy\x01\t\x17\x01\x01' ]
}
]
Expected: Third item in array has the correct lineItemOut, correct number of contractIdOut
[
{
contractLineIdOut: [ 10000000050 ],
contractIdOut: [ 100 ],
creationDateOut: [ 2021-01-01T05:00:00.000Z ],
lineItemOut: [ 'Line Item 1' ]
},
{
contractLineIdOut: [],
contractIdOut: [],
creationDateOut: [],
lineItemOut: []
},
{
contractLineIdOut: [ 10000000059 ],
contractIdOut: [ 800 ],
creationDateOut: [ 2021-01-10T05:00:00.000Z ],
lineItemOut: [ 'Line Item 8' ]
}
]
if the currency code was valid it would also update multiple rows
{
contractLineIdOut: [ 10000000055, 10000000056 ],
contractIdOut: [ 600, 600 ],
creationDateOut: [ 2021-01-06T05:00:00.000Z, 2021-01-07T05:00:00.000Z ],
lineItemOut: [ 'Line Item 6', 'Line Item 6' ]
},
- Include a runnable Node.js script that shows the problem.
let connection;
const createTableAndSeed = async () => {
try {
connection = await oracledb.getConnection({
user: oracleConnectionInfo.getKnexConnectionObject().user,
password: oracleConnectionInfo.getKnexConnectionObject().password,
connectString: oracleConnectionInfo.getConnectionString(),
});
const creationTable = `
CREATE TABLE REPRODUCTION_CONTRACT_TABLE (
CONTRACT_LINE_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY INCREMENT BY 1 START WITH 10000000000 MAXVALUE 999999999999999999999999999 CACHE 1000
, CONTRACT_ID NUMBER NOT NULL
, CREATION_DATE DATE
, CURRENCY_CODE VARCHAR2(20)
, LINE_ITEM VARCHAR2(500)
, CONSTRAINT CONTRACT_LINE_PK PRIMARY KEY (CONTRACT_LINE_ID) ENABLE
);
`;
await connection.execute(creationTable);
const sql = `
INSERT INTO REPRODUCTION_CONTRACT_TABLE
(CONTRACT_ID, CREATION_DATE, CURRENCY_CODE, LINE_ITEM)
VALUES (:contract_id, TO_DATE(:creation_date, 'YYYY-MM-DD'), :currency_code, :line_item)
`;
const binds = [
{ contract_id: 100, creation_date: '2021-01-01', currency_code: 'USD', line_item: 'Line Item 1' },
{ contract_id: 200, creation_date: '2021-01-02', currency_code: 'USD', line_item: 'Line Item 2' },
{ contract_id: 300, creation_date: '2021-01-03', currency_code: 'USD', line_item: 'Line Item 3' },
{ contract_id: 400, creation_date: '2021-01-04', currency_code: 'USD', line_item: 'Line Item 4' },
{ contract_id: 500, creation_date: '2021-01-05', currency_code: 'USD', line_item: 'Line Item 5' },
{ contract_id: 600, creation_date: '2021-01-06', currency_code: 'USD', line_item: 'Line Item 6' },
{ contract_id: 600, creation_date: '2021-01-07', currency_code: 'USD', line_item: 'Line Item 6' },
{ contract_id: 700, creation_date: '2021-01-08', currency_code: 'USD', line_item: 'Line Item 7' },
{ contract_id: 700, creation_date: '2021-01-09', currency_code: 'USD', line_item: 'Line Item 7' },
{ contract_id: 800, creation_date: '2021-01-10', currency_code: 'USD', line_item: 'Line Item 8' },
];
const options = {
autoCommit: true, // Automatically commit after each insert
bindDefs: {
contract_id: { type: oracledb.NUMBER },
creation_date: { type: oracledb.STRING, maxSize: 10 },
currency_code: { type: oracledb.STRING, maxSize: 3 },
line_item: { type: oracledb.STRING, maxSize: 50 },
},
};
const result = await connection.executeMany(sql, binds, options);
console.log('Rows inserted:', result.rowsAffected);
} catch (err) {
console.log(err);
} finally {
connection.close();
}
};
const update = async () => {
const update = `
update REPRODUCTION_CONTRACT_TABLE
SET
CONTRACT_ID =:contractId,
CURRENCY_CODE =:currencyCode
WHERE LINE_ITEM =:lineItem
RETURNING CONTRACT_LINE_ID, CONTRACT_ID, CREATION_DATE, LINE_ITEM INTO :contractLineIdOut, :contractIdOut, :creationDateOut, :lineItemOut
`;
const binds = [
{
contractId: 100,
currencyCode: 'USD',
lineItem: 'Line Item 1',
},
{
contractId: 600,
currencyCode: 'USD Values Is Way To Long To Fit In The Column',
lineItem: 'Line Item 6',
},
{
contractId: 800,
currencyCode: 'USD',
lineItem: 'Line Item 8',
},
];
const options = {
autoCommit: true,
batchErrors: true,
bindDefs: {
contractId: { type: oracledb.NUMBER },
currencyCode: { maxSize: 512, type: oracledb.STRING },
lineItem: { type: oracledb.STRING, maxSize: 512 },
contractLineIdOut: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
contractIdOut: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
creationDateOut: { type: oracledb.DATE, dir: oracledb.BIND_OUT },
lineItemOut: { type: oracledb.STRING, dir: oracledb.BIND_OUT, maxSize: 512 },
},
};
let result;
let connection;
try {
connection = await oracledb.getConnection({
user: oracleConnectionInfo.getKnexConnectionObject().user,
password: oracleConnectionInfo.getKnexConnectionObject().password,
connectString: oracleConnectionInfo.getConnectionString(),
});
result = await connection.executeMany(update, binds, options);
} catch (err) {
console.log(err);
} finally {
connection.commit();
connection.close();
}
console.log(result?.batchErrors);
console.log(result?.outBinds);
};
await createTableAndSeed();
await update();
We appreciate you opening the issue, but the table and column names in the snippets don't match, and you haven't given the original data. Since details can matter when reproducing a bug, can you update the issue with a running script, and include the necessary INSERT statements?
I tried changing the binds to so that error would happen when updating a single row and had the same results
const binds = [
... original binds
{
contractId: 200,
currencyCode: 'USD Values Is Way To Long To Fit In The Column',
lineItem: 'Line Item 2',
}
];
// output
[
{
contractLineIdOut: [ 10000000050 ],
contractIdOut: [ 100 ],
creationDateOut: [ 2021-01-01T05:00:00.000Z ],
lineItemOut: [ 'Line Item 1' ]
},
{
contractLineIdOut: [],
contractIdOut: [],
creationDateOut: [],
lineItemOut: []
},
{
contractLineIdOut: [ 4.089930721500069e-28 ],
contractIdOut: [ 10000000059 ],
creationDateOut: [ 9309-01-01T12:00:00.000Z ],
lineItemOut: [ 'xy\x01\t\x17\x01\x01' ]
}
]
@BoxenOfDonuts , I think its because awaits are missing here. can you add await here in finally block.
await connection.commit();
await connection.close();
you can also add dmlRowCounts too to verify the updated rows and i could see the updated rows in my test.
const options = {
autoCommit: true,
batchErrors: true,
dmlRowCounts: true,
...
console.log(result.rowsAffected);
console.log(result?.dmlRowCounts);
just pasting the complete program of yours with minor modifications...
const createTableAndSeed = async () => {
try {
connection = await oracledb.getConnection(dbConfig);
const creationTable = `
CREATE TABLE REPRODUCTION_CONTRACT_TABLE (
CONTRACT_LINE_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY INCREMENT BY 1 START WITH 10000000000 MAXVALUE 999999999999999999999999999 CACHE 1000
, CONTRACT_ID NUMBER NOT NULL
, CREATION_DATE DATE
, CURRENCY_CODE VARCHAR2(20)
, LINE_ITEM VARCHAR2(500)
, CONSTRAINT CONTRACT_LINE_PK PRIMARY KEY (CONTRACT_LINE_ID) ENABLE
)
`;
await connection.execute(`drop table REPRODUCTION_CONTRACT_TABLE `);
await connection.execute(creationTable);
const sql = `
INSERT INTO REPRODUCTION_CONTRACT_TABLE
(CONTRACT_ID, CREATION_DATE, CURRENCY_CODE, LINE_ITEM)
VALUES (:contract_id, TO_DATE(:creation_date, 'YYYY-MM-DD'), :currency_code, :line_item)
`;
const binds = [
{ contract_id: 100, creation_date: '2021-01-01', currency_code: 'USD', line_item: 'Line Item 1' },
{ contract_id: 200, creation_date: '2021-01-02', currency_code: 'USD', line_item: 'Line Item 2' },
{ contract_id: 300, creation_date: '2021-01-03', currency_code: 'USD', line_item: 'Line Item 3' },
{ contract_id: 400, creation_date: '2021-01-04', currency_code: 'USD', line_item: 'Line Item 4' },
{ contract_id: 500, creation_date: '2021-01-05', currency_code: 'USD', line_item: 'Line Item 5' },
{ contract_id: 600, creation_date: '2021-01-06', currency_code: 'USD', line_item: 'Line Item 6' },
{ contract_id: 600, creation_date: '2021-01-07', currency_code: 'USD', line_item: 'Line Item 6' },
{ contract_id: 700, creation_date: '2021-01-08', currency_code: 'USD', line_item: 'Line Item 7' },
{ contract_id: 700, creation_date: '2021-01-09', currency_code: 'USD', line_item: 'Line Item 7' },
{ contract_id: 800, creation_date: '2021-01-10', currency_code: 'USD', line_item: 'Line Item 8' },
];
const options = {
autoCommit: true, // Automatically commit after each insert
bindDefs: {
contract_id: { type: oracledb.NUMBER },
creation_date: { type: oracledb.STRING, maxSize: 10 },
currency_code: { type: oracledb.STRING, maxSize: 3 },
line_item: { type: oracledb.STRING, maxSize: 50 },
},
};
const result = await connection.executeMany(sql, binds, options);
console.log('Rows inserted:', result.rowsAffected);
} catch (err) {
console.log(err);
} finally {
await connection.close();
}
};
const update = async () => {
const update = `
update REPRODUCTION_CONTRACT_TABLE
SET
CONTRACT_ID =:contractId,
CURRENCY_CODE =:currencyCode
WHERE LINE_ITEM =:lineItem
RETURNING CONTRACT_LINE_ID, CONTRACT_ID, CREATION_DATE, LINE_ITEM INTO :contractLineIdOut, :contractIdOut, :creationDateOut, :lineItemOut
`;
const binds = [
{
contractId: 600,
currencyCode: 'USD Values Is Way To Long To Fit In The Column',
lineItem: 'Line Item 6',
},
{
contractId: 1000,
currencyCode: 'USD',
lineItem: 'Line Item 1',
},
{
contractId: 8000,
currencyCode: 'USD',
lineItem: 'Line Item 8',
},
];
const options = {
autoCommit: true,
batchErrors: true,
dmlRowCounts: true,
bindDefs: {
contractId: { type: oracledb.NUMBER },
currencyCode: { maxSize: 512, type: oracledb.STRING },
lineItem: { type: oracledb.STRING, maxSize: 512 },
contractLineIdOut: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
contractIdOut: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT },
creationDateOut: { type: oracledb.DATE, dir: oracledb.BIND_OUT },
lineItemOut: { type: oracledb.STRING, dir: oracledb.BIND_OUT, maxSize: 512 },
},
};
let result;
let connection;
try {
connection = await oracledb.getConnection(dbConfig);
result = await connection.executeMany(update, binds, options);
} catch (err) {
console.log(err);
} finally {
await connection.commit();
}
console.log(result?.batchErrors);
console.log(result?.dmlRowCounts);
console.log(result.rowsAffected);
console.log(result?.outBinds);
result = await connection.execute('select * from REPRODUCTION_CONTRACT_TABLE');
console.log(result.rows);
await connection.close();
};
async function run() {
await createTableAndSeed();
await update();
}
run();
@BoxenOfDonuts , I think its because awaits are missing here. can you add await here in finally block.
@sudarshan12s that was just a small error in my reproduction code, the real code has the correct awaits. We have not had issues with the data actually being updated, just the bindings being offset on the returns. I did re-run the reproduction script and still see the wrong binding
// console.log(result?.batchErrors) ;
[
[Error: ORA-12899: value too large for column REPRODUCTION_CONTRACT_TABLE"."CURRENCY_CODE" (actual: 46, maximum: 20)] {
errorNum: 12899,
offset: 1
}
]
// console.log(result?.outBinds);
[
{
contractLineIdOut: [ 10000000050 ],
contractIdOut: [ 100 ],
creationDateOut: [ 2021-01-01T05:00:00.000Z ],
lineItemOut: [ 'Line Item 1' ]
},
{
contractLineIdOut: [],
contractIdOut: [],
creationDateOut: [],
lineItemOut: []
},
{
contractLineIdOut: [ 4.089930721500069e-28 ],
contractIdOut: [ 10000000059 ],
creationDateOut: [ 9309-01-01T12:00:00.000Z ],
lineItemOut: [ 'xy\x01\t\x17\x01\x01' ]
}
]
// console.log(result?.dmlRowCounts);
[ 1, 0, 1 ]
Thanks . We are checking the invalid outbinds returned and will update.