Error: ORA-01426: numeric overflow #322

sreenivasulun · 2016-01-13T07:45:32Z

I am getting the following error, when I made a call to pl/sql procedure,
Error: ORA-01426: numeric overflow
at Error (native)

as follows my code snippet,
'''
var sqlQuery = "BEGIN oe_web_txns.pre_web_SaleTrx(:source,:merchantID,:deviceID,:UMI,:dbConverter,"+
":trxTime, :origPA,:initialTrxId,:entityId,:postableAmount,:redemptionType,:adjustedAmount,:xlsTrxId,:errorCode,:errorDescription); END;";

var bindvars = {
source: saleObject.source,
merchantID: saleObject.merchantID,
deviceID: saleObject.deviceID,
UMI: saleObject.UMI,
dbConverter:saleObject.dbConverter,
trxTime:saleObject.trxTime,
origPA:parseFloat(saleObject.origPA),
initialTrxId:parseFloat(saleObject.initialTrxId),
entityId:parseFloat(entityId),
postableAmount:parseFloat(postableAmount),
redemptionType:parseFloat(redemptionType),
adjustedAmount: { type: oracledb.NUMBER, dir : oracledb.BIND_OUT },
xlsTrxId: { type: oracledb.NUMBER, dir : oracledb.BIND_OUT },
errorCode: { type: oracledb.NUMBER, dir : oracledb.BIND_OUT },
errorDescription: { type: oracledb.STRING, dir : oracledb.BIND_OUT }
}
'''
Please give some hits to solve this error,

Best regards,
Sreeni

cjbj · 2016-01-13T23:25:48Z

Identify the point of failure and look at the data being processed. Let us know what you find.

sreenivasulun · 2016-01-14T07:48:58Z

Thanks for your replay cjbj.
Sorry for not giving the below info in previous post.
This issue is occurring randomly (i.e., not every call) with same data, when I get same issue again, then I will post you the data which set in bindvars. We are not changing any data when error occurred and when error not occurred.
Basically our values,
origPA = 10, initialTrxId=322080, entityId=2, postableAmount=2, redemptionType=2, source="20", merchantID="989800000000000", deviceID="98980002", UMI="/XM/300000000000/%252FXM%252F1FE3EE76AC1DD07620C3CBF6C3BCFADD%252F1601"

Thanks & regards,
Sreeni

cjbj · 2016-01-18T12:49:50Z

I'll close this for the moment until you have more information.

sreenivasulun · 2016-01-19T03:09:56Z

Dear CJBJ,
Did you get any clue with the data sample posted in previous post?.
We are facing this issue since long time. I am Node.JS developer here and not able to find solution for it.
Even checked with the expert database engineer, he is also not able find solution for it.
When we get this error, oracledb module is not printing much stacktrace to see what actually causing it.
That too it is not occurring always, to debug on that specific time.

If you find clues please let us know. This issue become big problem for me. :(

Best regards,
Sreeni

cjbj · 2016-01-19T03:23:53Z

Keep working on it. When you identify some data which fails, post a testcase here and include the SQL to create tables, the PL/SQL etc.

amjadparacha · 2016-01-21T07:47:38Z

Hi,
I was having similar issue when i'm launching 100 concurrent calls, for single call it was working fine, sometimes my node server got stuck and cause this issue even for a single call, after spending almost 8 hours in searching, I figured out that it was because of numeric output variable which I'm using in my stored procedure, once I removed it everything is now working fine.

It seems like memory management for number bind out data type in node-oracledb lib is not efficient enough and causing numeric overflow error.

cjbj · 2016-01-21T09:02:28Z

'Efficient enough'? Can you explain?

You could be hitting a boundary case where JS types and Oracle NUMBER (if that's what you were using) cannot represent the same values.

Bottom line: please send a testcase so we can look at it.

amjadparacha · 2016-01-21T09:17:17Z

I'm using this "{type: oracledb.NUMBER, dir: oracledb.BIND_OUT}" to hold a return value.

Test case is very simple, just create a sequence in oracle and use output variable to return it from stored procedure, now launch concurrent requests, at some point your'll receive this error, in my case I got this error when sequence returned a 5 digit number.

cjbj · 2016-01-21T11:04:34Z

@amjadparacha please send me the files that reproduce it. Thanks!

cjbj · 2016-01-22T08:02:49Z

@amjadparacha @sreenivasulun we couldn't reproduce it. Please send a testcase and/or give us all the detail possible: platforms, versions of OS, of Oracle client and DB, version of Node etc

sreenivasulun · 2016-01-25T01:42:51Z

Hello @cjbj, thanks for your replies.
Hello @amjadparacha, thanks for your valuable comments. May be I can try not to return NUMBER type for BIND_OUT values.

Below are the softwares that I am using for my development,

Oracle database server - Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Node.js (node-v0.12.7-x64.msi)
MS studio 2013 64bit ( Visual Studio Community 2013 with Update 5 )
Python 2.7.10 msi
Oracle instant client (instantclient-basic-windows.x64-12.1.0.2.0 & instantclient-sdk-windows.x64-12.1.0.2.0)
OS - Windows 7 Professional SP1 64Bit
oracledb module - 1.3.0
kraken.js - 1.0.3

I already posted above the pl/sql procedure, actual test data and my bindvars.
Even I tried with [email protected] and [email protected] , still the same error is coming some times.

Regards,
@sreenivasulun

cjbj · 2016-01-25T01:53:33Z

@sreenivasulun 3 different people tried 3 different scripts and didn't reproduce it. Can you put together a complete testcase? Thanks.

amjadparacha · 2016-01-25T06:58:02Z

@cjbj

Sorry for the delayed response, I'm using following script, kindly execute it as per my defined use case above;

var bindInsert = {
O_LOG_ID: {type: oracledb.NUMBER, dir: oracledb.BIND_OUT},
P_TRANSACTION_ID: String(result.LocationRequestMessage.RequestHeader[0].TranscationID[0]),
P_REQUEST_DATE: new Date().toISOString().replace(/T/, ' ').replace(/..+/, ''),
O_ErrorCode: {type: oracledb.STRING, dir: oracledb.BIND_OUT, maxSize: 40},
O_ErrorDesc: {type: oracledb.STRING, dir: oracledb.BIND_OUT, maxSize: 400}
};

function LogTransactionRequest(bindInsert, res, cb) {
oracledb.getConnection(connAttrs, function(err, connection) {
if(err){
res.set('Content-Type', 'application/json');
res.status(500).send(JSON.stringify({
status: 500,
message: "Error connecting to DB",
detailed_message: err.message
}));
return;
}

connection.execute("BEGIN LLGG_USER_PROFILE.insert_record(:O_LOG_ID, :P_TRANSACTION_ID, :P_REQUEST_DATE, :O_ErrorCode, :O_ErrorDesc); END;",    
    bindInsert,
    function (err, result) {
        if (err || result.rowsAffected === 0) {
            // Error
            console.log(err.stack);
        } else {
            cb(result.outBinds.O_ErrorCode);
            res.status(204).end();
        }
    // Release the connection
    connection.release(
            function (err) {
                if (err) {
                    console.error(err.message);
                } else {
                    console.log("Connection released");
                }
            });
    });
});

}

cjbj · 2016-01-29T07:27:47Z

@amjadparacha We need the PL/SQL and the CREATE TABLE etc commands too. And with the platform and versions you are using we will have a better chance of reproducing it. Thanks.
This is an import problem and I don't want it put on the 'too-hard pile' because we don't have a testcase that can be runnable. As I said earlier three of us wrote three different testcases and didn't hit it.

ronnn · 2016-03-02T08:25:25Z

We ran into this issue a few days ago. I tried to build a minimal example to reproduce but it seems as if the error only occurs every now and then. We'll investigate further.

cjbj · 2016-03-02T09:24:50Z

@ronnn can you share details about OS, client, server & node versions, what kinds of datatypes you are using?

ronnn · 2016-03-02T12:35:32Z

@cjbj

Windows 7 Enterprise Service Pack 1 64bit
Instant Client 12.1.0.2.0 64bit
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Node 4.2.6 64bit
Python 2.7.6
Microsoft Visual Studio Express 2013 64 Bit with Update 4
oracledb 1.7.0
Furthermore we use orawrap 0.2.4 for connection pooling.

We have serveral packages for data manipulation that are called from our models. Until now the error only popped up, when inserting a new record via a function inside one of these packages. The function returns a NUMBER (ID of the new record, generated from sequence).

For example:

function create_user(
    i_username in USERS.USERNAME%TYPE,
    i_first_name in USERS.FIRST_NAME%TYPE,
    i_last_name in USERS.LAST_NAME%TYPE,
    i_email in USERS.EMAIL%TYPE,
    i_language_code in USERS.LANGUAGE_CODE%TYPE,
    i_ldap in USERS.LDAP%TYPE
)
return USERS.ID%TYPE
is
    lv_user_id USERS.ID%TYPE;
begin
    insert
    into USERS
    (
        ID,
        USERNAME,
        FIRST_NAME,
        LAST_NAME,
        EMAIL,
        LDAP,
        LANGUAGE_CODE
    )
    VALUES
    (
        USER_ID_SEQ.NEXTVAL,
        upper(i_username),
        i_first_name,
        i_last_name,
        lower(i_email),
        i_ldap,
        i_language_code
    )
    RETURNING ID
    into lv_user_id;

    return lv_user_id;
end create_user;

Called by:

exports.create = function (connection, user, next) {
    connection.execute(
        'BEGIN :user_id := USER_PKG.CREATE_USER( \
            :username,  \
            :first_name,  \
            :last_name,  \
            :email,  \
            :language_code,  \
            :ldap  \
        ); END;', {
            username: user.USERNAME,
            first_name: user.FIRST_NAME,
            last_name: user.LAST_NAME,
            email: user.EMAIL,
            language_code: user.LANGUAGE_CODE,
            ldap: user.LDAP,
            user_id: {
                type: orawrap.NUMBER,
                dir: orawrap.BIND_OUT
            }
        },
        function (err, results) {
            if (err) {
                return next(err);
            }

            next(null, {
                user_id: results.outBinds.user_id
            });
        });
};

Another team in our company is developing on a Linux virtual machine using the same node-modules and a similar application structure. They didn't receive the error so far.

dmcghan · 2016-03-04T18:02:31Z

@sreenivasulun @ronnn Since we can't get a reproducable test case, could you try adding some logging code to help isolate the issue? Have a look at logger: https://github.com/OraOpenSource/Logger

Once installed, you can add an exception handler like:

exception
  when others
  then
    logger.logError();

This will provide a lot of information in the logger_logs table. Also, you to pass in some context variables, such as the ids being returned, to help figure out what went wrong.

ronnn · 2016-03-08T09:28:10Z

@cjbj @dmcghan We added the Logger and it is logging all errors (unique constraints etc.) except the numeric overflow.

The node output is as follows:
[Error: ORA-01426: numeric overflow]

EDIT
We activated the trace logging of the instant client and this is the output when the error occured:

(9944) [08-MAR-2016 12:08:53:462] nioqsn: entry
(9944) [08-MAR-2016 12:08:53:462] nioqsn: exit
(9944) [08-MAR-2016 12:08:53:462] nioqrs: entry
(9944) [08-MAR-2016 12:08:53:462] nioqrs:  state = interrupted (1)
(9944) [08-MAR-2016 12:08:53:462] nscontrol: entry
(9944) [08-MAR-2016 12:08:53:462] nscontrol: cmd=45, lcl=0x0
(9944) [08-MAR-2016 12:08:53:462] nscontrol: normal exit
(9944) [08-MAR-2016 12:08:53:462] nscontrol: entry
(9944) [08-MAR-2016 12:08:53:462] nscontrol: cmd=1, lcl=0x0
(9944) [08-MAR-2016 12:08:53:462] nscontrol: normal exit
(9944) [08-MAR-2016 12:08:53:462] nioqsm: entry
(9944) [08-MAR-2016 12:08:53:462] nioqsm:  Sending break packet (1)...
(9944) [08-MAR-2016 12:08:53:462] nscontrol: entry
(9944) [08-MAR-2016 12:08:53:462] nscontrol: cmd=45, lcl=0x0
(9944) [08-MAR-2016 12:08:53:462] nscontrol: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdo: entry
(9944) [08-MAR-2016 12:08:53:462] nsdo: cid=0, opcode=67, *bl=1, *what=17, uflgs=0x100, cflgs=0x3
(9944) [08-MAR-2016 12:08:53:462] snsbitts_ts: entry
(9944) [08-MAR-2016 12:08:53:462] snsbitts_ts: acquired the bit
(9944) [08-MAR-2016 12:08:53:462] snsbitts_ts: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdo: rank=64, nsctxrnk=0
(9944) [08-MAR-2016 12:08:53:462] snsbitcl_ts: entry
(9944) [08-MAR-2016 12:08:53:462] snsbitcl_ts: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdo: nsctx: state=8, flg=0x400d, mvd=0
(9944) [08-MAR-2016 12:08:53:462] nsdo: gtn=127, gtc=127, ptn=10, ptc=8111
(9944) [08-MAR-2016 12:08:53:462] nsdofls: entry
(9944) [08-MAR-2016 12:08:53:462] nsdofls: DATA flags: 0x0
(9944) [08-MAR-2016 12:08:53:462] nsdofls: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdo: sending NSPTMK packet
(9944) [08-MAR-2016 12:08:53:462] nspsend: entry
(9944) [08-MAR-2016 12:08:53:462] nspsend: plen=11, type=12
(9944) [08-MAR-2016 12:08:53:462] nttwr: entry
(9944) [08-MAR-2016 12:08:53:462] nttwr: socket 828 had bytes written=11
(9944) [08-MAR-2016 12:08:53:462] nttwr: exit
(9944) [08-MAR-2016 12:08:53:462] nspsend: packet dump
(9944) [08-MAR-2016 12:08:53:462] nspsend: 00 0B 00 00 0C 00 00 00  |........|
(9944) [08-MAR-2016 12:08:53:462] nspsend: 01 00 01                 |...     |
(9944) [08-MAR-2016 12:08:53:462] nspsend: 11 bytes to transport
(9944) [08-MAR-2016 12:08:53:462] nspsend: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdoacts: entry
(9944) [08-MAR-2016 12:08:53:462] nsdofls: entry
(9944) [08-MAR-2016 12:08:53:462] nsdofls: DATA flags: 0x0
(9944) [08-MAR-2016 12:08:53:462] nsdofls: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdoacts: flushing transport
(9944) [08-MAR-2016 12:08:53:462] nttctl: entry
(9944) [08-MAR-2016 12:08:53:462] nsdoacts: normal exit
(9944) [08-MAR-2016 12:08:53:462] snsbitts_ts: entry
(9944) [08-MAR-2016 12:08:53:462] snsbitts_ts: acquired the bit
(9944) [08-MAR-2016 12:08:53:462] snsbitts_ts: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdo: nsctxrnk=0
(9944) [08-MAR-2016 12:08:53:462] snsbitcl_ts: entry
(9944) [08-MAR-2016 12:08:53:462] snsbitcl_ts: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdo: normal exit
(9944) [08-MAR-2016 12:08:53:462] nioqsm: exit
(9944) [08-MAR-2016 12:08:53:462] nioqrs:  state = interrupted (1)
(9944) [08-MAR-2016 12:08:53:462] nioqrs: nioqrs: sending reset marker...
(9944) [08-MAR-2016 12:08:53:462] nioqsm: entry
(9944) [08-MAR-2016 12:08:53:462] nioqsm:  Sending reset packet (2)...
(9944) [08-MAR-2016 12:08:53:462] nscontrol: entry
(9944) [08-MAR-2016 12:08:53:462] nscontrol: cmd=45, lcl=0x0
(9944) [08-MAR-2016 12:08:53:462] nscontrol: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdo: entry
(9944) [08-MAR-2016 12:08:53:462] nsdo: cid=0, opcode=67, *bl=1, *what=17, uflgs=0x0, cflgs=0x3
(9944) [08-MAR-2016 12:08:53:462] snsbitts_ts: entry
(9944) [08-MAR-2016 12:08:53:462] snsbitts_ts: acquired the bit
(9944) [08-MAR-2016 12:08:53:462] snsbitts_ts: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdo: rank=64, nsctxrnk=0
(9944) [08-MAR-2016 12:08:53:462] snsbitcl_ts: entry
(9944) [08-MAR-2016 12:08:53:462] snsbitcl_ts: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdo: nsctx: state=8, flg=0x400d, mvd=0
(9944) [08-MAR-2016 12:08:53:462] nsdo: gtn=127, gtc=127, ptn=10, ptc=8111
(9944) [08-MAR-2016 12:08:53:462] nsdofls: entry
(9944) [08-MAR-2016 12:08:53:462] nsdofls: DATA flags: 0x0
(9944) [08-MAR-2016 12:08:53:462] nsdofls: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdo: sending NSPTMK packet
(9944) [08-MAR-2016 12:08:53:462] nspsend: entry
(9944) [08-MAR-2016 12:08:53:462] nspsend: plen=11, type=12
(9944) [08-MAR-2016 12:08:53:462] nttwr: entry
(9944) [08-MAR-2016 12:08:53:462] nttwr: socket 828 had bytes written=11
(9944) [08-MAR-2016 12:08:53:462] nttwr: exit
(9944) [08-MAR-2016 12:08:53:462] nspsend: packet dump
(9944) [08-MAR-2016 12:08:53:462] nspsend: 00 0B 00 00 0C 00 00 00  |........|
(9944) [08-MAR-2016 12:08:53:462] nspsend: 01 00 02                 |...     |
(9944) [08-MAR-2016 12:08:53:462] nspsend: 11 bytes to transport
(9944) [08-MAR-2016 12:08:53:462] nspsend: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdoacts: entry
(9944) [08-MAR-2016 12:08:53:462] nsdofls: entry
(9944) [08-MAR-2016 12:08:53:462] nsdofls: DATA flags: 0x0
(9944) [08-MAR-2016 12:08:53:462] nsdofls: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdoacts: flushing transport
(9944) [08-MAR-2016 12:08:53:462] nttctl: entry
(9944) [08-MAR-2016 12:08:53:462] nsdoacts: normal exit
(9944) [08-MAR-2016 12:08:53:462] snsbitts_ts: entry
(9944) [08-MAR-2016 12:08:53:462] snsbitts_ts: acquired the bit
(9944) [08-MAR-2016 12:08:53:462] snsbitts_ts: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdo: nsctxrnk=0
(9944) [08-MAR-2016 12:08:53:462] snsbitcl_ts: entry
(9944) [08-MAR-2016 12:08:53:462] snsbitcl_ts: normal exit
(9944) [08-MAR-2016 12:08:53:462] nsdo: normal exit
(9944) [08-MAR-2016 12:08:53:462] nioqsm: exit
(9944) [08-MAR-2016 12:08:53:462] nioqrs: nioqrs: sucking for reset marker...
(9944) [08-MAR-2016 12:08:53:462] nioqar: entry
(9944) [08-MAR-2016 12:08:53:462] nioqar: nioqar: suck pipe til I get a reset...
(9944) [08-MAR-2016 12:08:53:462] nsbasic_brc: entry: oln/tot=0
(9944) [08-MAR-2016 12:08:53:462] nttfprd: entry
(9944) [08-MAR-2016 12:08:53:586] nttfprd: socket 828 had bytes read=119
(9944) [08-MAR-2016 12:08:53:586] nttfprd: exit
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: type=12, plen=11
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: what=17, tot =119
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: packet dump
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 00 0B 00 00 0C 00 00 00  |........|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 01 00 02                 |...     |
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: exit: oln=108, dln=1, tot=119, rc=0
(9944) [08-MAR-2016 12:08:53:586] nioqar: exit
(9944) [08-MAR-2016 12:08:53:586] nsnactl: entry
(9944) [08-MAR-2016 12:08:53:586] nactl_internal: entry
(9944) [08-MAR-2016 12:08:53:586] naeectl: entry
(9944) [08-MAR-2016 12:08:53:586] naeectl: exit
(9944) [08-MAR-2016 12:08:53:586] naecctl: entry
(9944) [08-MAR-2016 12:08:53:586] naecctl: exit
(9944) [08-MAR-2016 12:08:53:586] nactl_internal: exit
(9944) [08-MAR-2016 12:08:53:586] nsnactl: error exit
(9944) [08-MAR-2016 12:08:53:586] nserror: entry
(9944) [08-MAR-2016 12:08:53:586] nserror: nsres: id=0, op=77, ns=12630, ns2=0; nt[0]=0, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
(9944) [08-MAR-2016 12:08:53:586] nioqer: entry
(9944) [08-MAR-2016 12:08:53:586] nioqer:  incoming err = 0
(9944) [08-MAR-2016 12:08:53:586] nioqce: entry
(9944) [08-MAR-2016 12:08:53:586] nioqce: exit
(9944) [08-MAR-2016 12:08:53:586] nioqer:  returning err = 0
(9944) [08-MAR-2016 12:08:53:586] nioqer: exit
(9944) [08-MAR-2016 12:08:53:586] nioqrs: exit
(9944) [08-MAR-2016 12:08:53:586] nioqrc: entry
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: entry: oln/tot=108
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: type=6, plen=108
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: what=1, tot =108
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: packet dump
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 00 6C 00 00 06 00 00 00  |.l......|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 00 00 04 01 05 01 15 01  |........|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 01 02 03 F5 00 00 01 06  |........|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 00 2F 00 00 00 00 00 03  |./......|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 03 A7 75 01 1E 00 03 0A  |..u.....|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: F0 A6 00 00 00 19 00 01  |........|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 01 00 00 00 00 36 4F 52  |.....6OR|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 41 2D 30 31 30 31 33 3A  |A-01013:|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 20 75 73 65 72 20 72 65  |.user.re|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 71 75 65 73 74 65 64 20  |quested.|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 63 61 6E 63 65 6C 20 6F  |cancel.o|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 66 20 63 75 72 72 65 6E  |f.curren|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 74 20 6F 70 65 72 61 74  |t.operat|
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: 69 6F 6E 0A              |ion.    |
(9944) [08-MAR-2016 12:08:53:586] nsbasic_brc: exit: oln=0, dln=98, tot=108, rc=0
(9944) [08-MAR-2016 12:08:53:586] nioqrc: exit

dmcghan · 2016-03-09T04:43:13Z

@ronnn Try adding a call to logger that logs the return value of USER_PKG.CREATE_USER just prior to returning it. Then check the log and you should find the value that is causing issues when you get an error. You may need to log some kind of context variable from the node code to help you associate the error with the log entry.

sreenivasulun · 2016-03-09T09:08:55Z

Thank you @ronnn, @dmcghan for participating on this issue. Just checked all your comments, thanks for it. As we still not solved this issue, I will try to test my application based on your comments, if I find any, will revert the same.

Thank you

ronnn · 2016-03-09T09:15:40Z

@dmcghan We added log calls before and after every INSERT in all of our create functions and LOGGER_LOGS is only filled when no numeric overflow is thrown. Doesn't this indicate that the origin of the error is somewhere on the client side?

cjbj · 2016-03-10T04:25:33Z

@ronnn Can you do some logging inside the procedure to see what value(s) are supposed to be returned?

cjbj · 2016-03-10T06:13:06Z

@ronnn for completeness, what are your CREATE TABLE & CREATE SEQUENCE statements?
What does your next() method do with the new id?

ronnn · 2016-03-10T08:21:30Z

@cjbj We already did that. Nothing is added to the log, when the error is thrown.

function create_user(
    i_username in USERS.USERNAME%TYPE,
    i_first_name in USERS.FIRST_NAME%TYPE,
    i_last_name in USERS.LAST_NAME%TYPE,
    i_email in USERS.EMAIL%TYPE,
    i_language_code in USERS.LANGUAGE_CODE%TYPE,
    i_ldap in USERS.LDAP%TYPE
)
return USERS.ID%TYPE
is
    lv_user_id USERS.ID%TYPE;
begin
    logger_user.logger.log('before insert');

    insert
    into USERS
    (
        ID,
        USERNAME,
        FIRST_NAME,
        LAST_NAME,
        EMAIL,
        LDAP,
        LANGUAGE_CODE
    )
    VALUES
    (
        USER_ID_SEQ.NEXTVAL,
        upper(i_username),
        i_first_name,
        i_last_name,
        lower(i_email),
        i_ldap,
        i_language_code
    )
    RETURNING ID
    into lv_user_id;

    logger_user.logger.log('create_user returning: ' || lv_user_id);

    return lv_user_id;
end create_user;

CREATE TABLE USERS
(
  ID NUMBER NOT NULL,
  USERNAME VARCHAR2 (255) NOT NULL,
  FIRST_NAME VARCHAR2 (255) NOT NULL,
  LAST_NAME VARCHAR2 (255) NOT NULL,
  EMAIL VARCHAR2 (255) NOT NULL,
  LDAP CHAR (1) DEFAULT 'Y' NOT NULL,
  LANGUAGE_CODE VARCHAR2 (2) DEFAULT 'de' NOT NULL
);

CREATE SEQUENCE USER_ID_SEQ;

In this case (creation of user) next is doing nothing with the ID. At other places we use the ID to select the object and return it to the frontend.

cjbj · 2016-05-13T02:20:02Z

Stil haven't been able to reproduce. A complete testcase with data would be helpful.

cjbj · 2016-06-06T11:01:32Z

Closing this until someone comes up with a reproducible case. It's not clear if the problem still exists.

jhwetstone · 2016-06-23T21:43:07Z

Hi,

Our application is also experiencing the same issue. Details are:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Windows 7 Enterprise 64-bit
64-bit node 4.4.3
InstantClient 12.1.0.2.0 for 64-bit Windows
oracledb 1.8.0

We are experiencing the issue intermittently, with various procedure calls that have NUMBER out-binds.

Example logging info is:

{ [Error: ORA-01426: numeric overflow]
 cause:
   [Error: ORA-01426: numeric overflow
   ],
  isOperational: true }
call sp_AllocationAddFromIMS(:ranking_id, :import_id, :user_id, :allocation_id,
:is_new_allocation, :created_date)
{ ranking_id: 1549536,
  import_id: 4343895,
  user_id: 'admin',
  allocation_id: { type: 2002, dir: 3003 },
  is_new_allocation: { type: 2002, dir: 3003 },
  created_date: { type: 2001, dir: 3003 } }

Running that exact command immediately afterwards from SQLDeveloper:

  DECLARE 
    allocation_id NUMBER;
    is_new_allocation NUMBER;
    created_date VARCHAR(30);
  BEGIN  
    sp_AllocationAddFromIMS(1549536,4343895,'admin',allocation_id,is_new_allocation,created_date);
    DBMS_OUTPUT.PUT_LINE(allocation_id);
    DBMS_OUTPUT.PUT_LINE(is_new_allocation);
    DBMS_OUTPUT.PUT_LINE(created_date);
  END;

Results in no errors and an output of:

15518649
0
06/23/16

We have a deployed version of the code on a Linux server, and now that I've read this chain I actually am wondering if we have ever seen this error there. We are going through SIT on our application now, so I will be keeping an eye out for this error in the application logs.

Let me know if I can provide any additional information to help diagnose.

cjbj · 2016-06-24T05:56:51Z

Are you setting all 3 bind values in all PL/SQL code paths?
If you have a testcase...

PS I would recommend going to 1.9.3 for the various fixes it has

jhwetstone · 2016-06-24T15:56:38Z

Yes, those three out-binds are always set.

CREATE OR REPLACE PROCEDURE sp_AllocationAddFromIMS
(
   ranking_id IN number,
   import_id IN number,
   user_id IN VARCHAR2,
   allocation_id OUT number,
   is_new_allocation OUT number,
   created_date OUT VARCHAR2

)
AS
instore_date VARCHAR2(30 BYTE);
release_option VARCHAR2(30 BYTE);
release_status VARCHAR2(30 BYTE);
store_quantity_createddate VARCHAR2(30 BYTE);
region_number NUMBER;
district_number NUMBER;
item_number NUMBER;
store_number NUMBER;
quantity NUMBER;
date_added VARCHAR2(30 BYTE);
BEGIN
  BEGIN
    SELECT TO_CHAR(i.INSTOREDATE, 'MM/DD/YY'),
      ITEMNUMBER,
      RELEASEOPTION,
      RELEASESTATUS,
      STORENUMBER,
      --IMS store number is 1, then 4 digit store number. convert to actual store number
      STOREQUANTITY,
      TO_CHAR(i.DATEADDED, 'MM/DD/YY')
    INTO instore_date,
         item_number,
         release_option,
         release_status,
         store_number,
         quantity,
         date_added
    FROM IMSALLOCATIONSIMPORT I
    WHERE IMSALLOCATIONSIMPORTID = import_id;

    SELECT ALLOCATIONID, TO_CHAR(a.CREATETIMESTAMP, 'MM/DD/YY') into allocation_id, created_date
    FROM ALLOCATIONS a
    WHERE a.RANKINGID = ranking_id
      AND a.RELEASEOPTION = release_option
      AND a.INSTOREDATE = TO_DATE(instore_date, 'MM/DD/YY')
      AND a.ITEMNUMBER = item_number
      AND a.SOURCEOFALLOCATION = 'IMS';

    is_new_allocation := 0;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    BEGIN
      is_new_allocation := 1;
      sp_AllocationAdd(ranking_id,item_number,release_option,'IMS',release_status,instore_date,user_id,date_added, allocation_id, created_date);
    END;
    WHEN OTHERS THEN RAISE;
  END; --end create allocation block

  IF is_new_allocation = 0 THEN
    UPDATE  ALLOCATIONS
      SET   ISDELETED = 0,
            ALLOCATIONSTATUS = release_status,
            LASTMODIFIEDTIMESTAMP = CURRENT_TIMESTAMP,
            LASTMODIFIEDUSERID = user_id
    WHERE   ALLOCATIONID = allocation_id;
  END IF;

  sp_StoreQuantityModify(allocation_id, store_number, quantity, user_id, store_quantity_createddate, region_number, district_number);

  COMMIT;
END;
/

I can provide the PL/SQL for all of the procs and tables later today if that would be helpful - do you also need the nodejs code?

I'm also updating our oracledb version now, thanks for the call out.

cjbj · 2016-06-29T02:54:10Z

@jhwetstone if all OUT binds are set in the PL/SQL block, then it probably isn't related to #344.

I'd be interested in knowing whether you have seen it on Linux. All the reports so far have been on Windows.

If can bundle up your schema & PL/SQL & JS into a runnable testcase we can throw some load at it and see if we can reproduce it. Email me (see profile)

electrotype · 2017-02-20T20:35:05Z

I just want to let you know that we also have this issue (with version 1.12.2). Using Artillery to run multiple requests, sometimes the 1-5 first requests result in that "numeric overflow" errors but all the following are ok .

I'm also on Windows 7. I can't provide any code for the moment, sadly. But I can add some logging or try some workarounds if it can help. I'm going to try to run the tests in Docker (based on the official Node image) to see if I'm able to reproduce the problem.

electrotype · 2017-02-21T17:22:31Z

I can now confirm that running exactly the same code from Docker works without any problem. The "numeric overflow" error only occurs when the oracledb dependency is installed and used on Windows.

cjbj · 2017-02-21T19:02:30Z

It's been a while since we saw any reports of this. Windows is common to all. We do need a testcase that reproduces for us.

I wonder if the future node-oracledb v2 will exhibit it?

joshholl · 2017-03-18T03:47:28Z

I know this is oldish but I am having this issue occur semi frequently (~10-20% of the time)
My environment is
node 6.7.0
windows 7
visual studio 2015
oci.dll 11.2.0.1
oracledb 1.11 (also happens on 1.13)

gist that produces the issue

I also was able to capture a memory dump with the exception that I can provide but would rather send securely(if necessary)

Let me know if you would like any other info or for me to try anything else.

michaelabuckley · 2017-03-20T21:05:28Z

This happens to me on OSX with instantclient-basic-macos.x64-11.2.0.4.0.zip, node 4.4.7, node-oracledb version 1.11.0. It's happened with two different calls, always with NUMBER out binds. It's been happening for months, going back to when I first tried this library (1.8). It's never happened in production (Linux).

I'll upgrade node-oracledb and see.

bujji-vaila · 2017-05-04T08:09:30Z

Hi All,
I faced this issue when i call stored procedure, I tried change the OUT parameter "NUMBER" to "STRING"
it works for me, no more facing "Numeric overflow" issue.

ex;
adjustedAmount: { type: oracledb.NUMBER, dir : oracledb.BIND_OUT },
xlsTxnID: { type: oracledb.NUMBER, dir : oracledb.BIND_OUT },
errorCode: { type: oracledb.NUMBER, dir : oracledb.BIND_OUT },

changed to
adjustedAmount: { type: oracledb.STRING, dir : oracledb.BIND_OUT },
xlsTxnID: { type: oracledb.STRING, dir : oracledb.BIND_OUT },
errorCode: { type: oracledb.STRING, dir : oracledb.BIND_OUT }

Thanks,
Bujji.

Pollocks01 · 2017-11-09T12:20:05Z

This happens sporadically for me too. It seems to happen most often on the first request after a period of inactivity. Node 7.5.0. If I retry the request, it tends to work first time. I've not been able to put together a reproducible test case: I too am on Windows 7 as a dev platform. I have scanned the available logs on my Linux box and don't see the error there either.

cjbj · 2017-11-09T23:15:31Z

@Pollocks01 thanks for letting us know. It's annoying. Have you tried v2?

cjbj added the question label Jan 13, 2016

cjbj closed this as completed Jan 18, 2016

cjbj reopened this Mar 10, 2016

cjbj added the bug label May 13, 2016

cjbj removed the question label May 13, 2016

cjbj closed this as completed Jun 6, 2016

cjbj mentioned this issue Jun 17, 2016

Stored proc with In-Out "id" parameter bound to "undefined" randomly fails #459

Closed

Error: ORA-01426: numeric overflow #322

Error: ORA-01426: numeric overflow #322

Comments

sreenivasulun commented Jan 13, 2016

cjbj commented Jan 13, 2016

sreenivasulun commented Jan 14, 2016

cjbj commented Jan 18, 2016

sreenivasulun commented Jan 19, 2016

cjbj commented Jan 19, 2016

amjadparacha commented Jan 21, 2016

cjbj commented Jan 21, 2016

amjadparacha commented Jan 21, 2016

cjbj commented Jan 21, 2016

cjbj commented Jan 22, 2016

sreenivasulun commented Jan 25, 2016

cjbj commented Jan 25, 2016

amjadparacha commented Jan 25, 2016

cjbj commented Jan 29, 2016

ronnn commented Mar 2, 2016

cjbj commented Mar 2, 2016

ronnn commented Mar 2, 2016

dmcghan commented Mar 4, 2016

ronnn commented Mar 8, 2016

dmcghan commented Mar 9, 2016

sreenivasulun commented Mar 9, 2016

ronnn commented Mar 9, 2016

cjbj commented Mar 10, 2016

cjbj commented Mar 10, 2016

ronnn commented Mar 10, 2016

cjbj commented May 13, 2016

cjbj commented Jun 6, 2016

jhwetstone commented Jun 23, 2016

cjbj commented Jun 24, 2016

jhwetstone commented Jun 24, 2016

cjbj commented Jun 29, 2016

electrotype commented Feb 20, 2017

electrotype commented Feb 21, 2017

cjbj commented Feb 21, 2017

joshholl commented Mar 18, 2017

michaelabuckley commented Mar 20, 2017

bujji-vaila commented May 4, 2017

Pollocks01 commented Nov 9, 2017

cjbj commented Nov 9, 2017