executeMany() offsets index of dbl returning variables on index #1683

BoxenOfDonuts · 2024-07-10T22:05:39Z

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();

The text was updated successfully, but these errors were encountered:

cjbj · 2024-07-11T00:57:52Z

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?

BoxenOfDonuts · 2024-07-11T14:54:07Z

@cjbj I updated the issue with a script and inserts

BoxenOfDonuts · 2024-07-11T16:29:58Z

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' ]
  }
]

sudarshan12s · 2024-07-17T18:18:39Z

@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 · 2024-07-17T21:55:12Z

@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 ]

sudarshan12s · 2024-07-18T07:47:15Z

Thanks . We are checking the invalid outbinds returned and will update.

BoxenOfDonuts added the bug label Jul 10, 2024

sudarshan12s mentioned this issue Jul 22, 2024

When batchErrors=true and dmlRowCounts=true the dmlRowCounts property returns values of 0's in a executeMany of a MERGE statement after first error is hit #1686

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

executeMany() offsets index of dbl returning variables on index #1683

executeMany() offsets index of dbl returning variables on index #1683

BoxenOfDonuts commented Jul 10, 2024 •

edited

Loading

cjbj commented Jul 11, 2024

BoxenOfDonuts commented Jul 11, 2024

BoxenOfDonuts commented Jul 11, 2024 •

edited

Loading

sudarshan12s commented Jul 17, 2024

BoxenOfDonuts commented Jul 17, 2024 •

edited

Loading

sudarshan12s commented Jul 18, 2024

executeMany() offsets index of dbl returning variables on index #1683

executeMany() offsets index of dbl returning variables on index #1683

Comments

BoxenOfDonuts commented Jul 10, 2024 • edited Loading

cjbj commented Jul 11, 2024

BoxenOfDonuts commented Jul 11, 2024

BoxenOfDonuts commented Jul 11, 2024 • edited Loading

sudarshan12s commented Jul 17, 2024

BoxenOfDonuts commented Jul 17, 2024 • edited Loading

sudarshan12s commented Jul 18, 2024

BoxenOfDonuts commented Jul 10, 2024 •

edited

Loading

BoxenOfDonuts commented Jul 11, 2024 •

edited

Loading

BoxenOfDonuts commented Jul 17, 2024 •

edited

Loading