Big numeric input bind variables are aproximated by node-oracledb

[gcantiza]

1. What versions are you using?

I'm using node-oracledb version 5.5.0 My dbversion is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production process.platform: 'win32' process.version: 'v16.17.1' process.arch: 'x64' require('oracledb').versionString '5.5.0' require('oracledb').oracleClientVersionString: '19.16.0.0.0'

2. Is it an error or a hang or a crash? It is an error

3. What error(s) or behavior you are seeing?

Big Numeric IN bind variables are aproximated by node-oracledb.

If I run:

const oracledb = require('oracledb')
oracledb.initOracleClient({ libDir: 'C:\\oracle\\product\\instantclient_19_16' });
oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
const dbConfig = require('./dbconfig');

async function doQuery() {
    oraclePool = await oracledb.createPool({ ...dbConfig, poolMin: 0, poolMax: 1, enableStatistics: true });
    connection = await oraclePool.getConnection();
    result = await connection.execute('select :1 bind_number, :2 bind_string, 1234567890123456 constant from dual', [1234567890123456, String(1234567890123456)]);
    console.log(result);
}

doQuery();

I get:

{
  metaData: [
    { name: 'BIND_NUMBER' },
    { name: 'BIND_STRING' },
    { name: 'CONSTANT' }
  ],
  rows: [
    {
      BIND_NUMBER: 1234567890123460,
      BIND_STRING: '1234567890123456',
      CONSTANT: 1234567890123456
    }
  ]
}

As you can see the first bind variable is recieved by Oracle as 1234567890123460 instead as the original value: 1234567890123456.

4. Include a runnable Node.js script that shows the problem.

   
   const oracledb = require('oracledb')
   oracledb.initOracleClient({ libDir: 'C:\\oracle\\product\\instantclient_19_16' });
   oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
   const dbConfig = require('./dbconfig');

async function doQuery() { oraclePool = await oracledb.createPool({ ...dbConfig, poolMin: 0, poolMax: 1, enableStatistics: true }); connection = await oraclePool.getConnection(); result = await connection.execute('select :1 bind_number, :2 bind_string, 1234567890123456 constant from dual', [1234567890123456, String(1234567890123456)]); console.log(result); }

doQuery();

[cjbj]

15 significant digits sounds right. Maybe @anthony-tuininga can comment.

[gcantiza]

As I see it is not rigth, if it is not declared or warned. Oracle uses 38 digits for numbers values. Why retruned numbers can hold more than 15 significant digits, but bind variables not? My use case was to retrieve a credit card using card number (16 digits). When I tried to retrieve credit card information by creditcard number it didn't return card information since it is altered by node-oracledb module. for example, used following query:

select cardholder from cards where card_number=:1

[anthony-tuininga]

@gcantiza, the problem is that Node.js uses double precision floating point as its internal type for "number". So you can only get 15-17 digits of precision -- and that depends heavily on the number. This can be seen by trying this in Node.js:

9007199254740993 === 9007199254740992

This will return the value true, even though the numbers are in fact different! This is discussed in this StackOverflow question.

The other thing that you stumbled onto is the fact that Oracle uses a different algorithm to perform conversions between decimal numbers and floating point numbers than is used by JavaScript -- which adds to the confusion. But it doesn't take away from the fact that you can't reliably use JavaScript "number" to represent (16 digit) credit card numbers. You will need to use "string" instead.

[gcantiza]

Thank you very much for your kind explanation and reference.