ExecuteMany inserting wrong values in NUMBER column: too large values instead of actual value.

[sanasz91mdev]

I am doing bulk insertion using executeMany in node-oracle db. It executes successfully , but the Numbers with decimal points are being inserted as a false very large value ...

Actual values are for eg. 102.22, 456672.89 but they are being inserted as shown in image below: AMOUNT is a NUMBER column.

CREATE TABLE some_schema.instructions (
    id                     NUMBER NOT NULL,
    batch_id               VARCHAR2(50),
    instruction_id         VARCHAR2(50),
    amount                 NUMBER, 
    account_title          VARCHAR2(200), 
    account_number         VARCHAR2(200), 
    account_holder_id      VARCHAR2(200),
    BENEFICIARY_NAME        VARCHAR2(200),
    DEBITOR_BIC             VARCHAR2(200),
    CREDITOR_BIC            VARCHAR2(200), 
    NARRATION               VARCHAR2(500),
    PAYMENT_PURPOSE         VARCHAR2(200)
);

ALTER TABLE some_schema.instructions ADD CONSTRAINT ins_pk PRIMARY KEY ( id );

   CREATE SEQUENCE some_schema.seq_instructions  MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;

==================================================== AMOUNT being inserted is a very large incorrect value as shown.

Also, in binds i am passing field : Number. check function below: makeBindsArray()

 async addInstructions(batchId, messages: any[], messageType = null) {
        try {
            logger.logInfo(`Inserting instructions against batch id: ${batchId}, type ${messageType}`);

            var bindsArray = [];

            let query = '';

                bindsArray = this.makeBindsArray(batchId, messages);
                query = `insert into some_schema.instructions (ID, BATCH_ID, INSTRUCTION_ID,AMOUNT,ACCOUNT_TITLE,ACCOUNT_NUMBER,ACCOUNT_HOLDER_ID, BENEFICIARY_NAME, DEBITOR_BIC, CREDITOR_BIC, NARRATION, PAYMENT_PURPOSE)
                    values (some_schema.seq_instructions.NEXTVAL, :batchId, :instructionId,:amount,:accounttitle,:accountnumber,:accountHolderId, :beneficiaryName, :debitorBic, :creditorBic, :narration, :paymentPurpose)`;

            let result = await this.executeManyAsync(query, bindsArray);

            if (result && result.rowsAffected > 0) {
                logger.logInfo(`Inserted instruction successfully against batch id ${batchId}, type ${messageType}`);
            } else {
                logger.logError(`Failed to insert Instructions against batch id ${batchId}, type ${messageType}`);
            }

            return result;
        }
        catch (error) {
            logger.logError(`Error occurred while inserting many instruction : ${error}`);
            throw error;
        }
    }

===================================================

makeBindsArray(batchId, messages: any[]) {
        let bindsArray = [];

        messages.forEach(item => {
            bindsArray.push([
                batchId,
                item.InstructionId,
                Number(item.Amount),
                item.AccountTitle,
                item.AccountNumber,
                item.AccountHolderId,
        tem.BeneficiaryName,
                item.DebitorBIC,
                item.CreditorBIC,
                item.Narration,
                item.PaymentPurpose
            ]
            );
        });

        return bindsArray;
    }

=====================================================

async executeManyAsync(query: string, binds: any = []) {
        let result_: any = {};
        try {
            this.logger_.log('executeManyAsync called');

            if (this.connection_ == undefined) {
                this.connection_ = await oracledb.getConnection(this.connAttrs)
            }

            var options = {
                autoCommit: true,           // autocommit if there are no batch errors
                batchErrors: true,          // identify invalid records; start a transaction for valid ones                
            };

            this.logger_.log('calling execute many');

            this.logger_.log('query = ' + query);
            this.logger_.log('binds = ' + binds);

            result_ = await this.connection_.executeMany(query, binds, options);

            this.logger_.logDebug('Number of rows affected = ' + result_.rowsAffected);

            if (result_.batchErrors) {
                this.logger_.logError(`executeMany raised some errors: [${JSON.stringify(result_.batchErrors)}].`);
            }

            return result_;
        }
        catch (err) {
            this.logger_.log(`error occurred at executeManyAsync: ${err.message}`);
            return null;
        }
        finally {
        }
    }

why is this happening? .. Is this a Javascript issue or node-oracle db's ExecuteMany issue?

I am using

database server: Oracle 19c, 12c oracledb version 3.1.2

process.arch 'x64' process.version 'v8.11.3' process.platform 'linux' require('oracledb').versionString '3.1.2' require('oracledb').oracleClientVersionString '12.1.0.1.0'

[cjbj]

• Don't use screen shots (not everyone can read them, and they are impossible to cut & paste to comment on, or to run).
• Please share a reproducible example, including the CREATE statement for the table, https://stackoverflow.com/help/minimal-reproducible-example
• Please answer the github issue logging questions https://raw.githubusercontent.com/oracle/node-oracledb/master/.github/ISSUE_TEMPLATE/general-questions-and-runtime-problems.md

[cjbj]

@sanasz91mdev any update from you on this?

[sanasz91mdev]

@sanasz91mdev any update from you on this?

@cjbj i have added code, ddl to reproduce this issue . Also added platform versions https://github.com/oracle/node-oracledb/issues/1346

[cjbj]

@sanasz91mdev Thanks for the updated questions. To go any further, we need a runnable test case, not individual code snippets. Importantly we need some data to insert.

[stale[bot]]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not been updated for a month.