Error serializing DB type with BigInt attribute

[adrianvpymnt]

1. What versions are you using?

"Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0"

oracledb version: 6.7.0
oracledb thin mode: false
oracle client version: 21.10.0.0.0

$ node --version
v18.18.0

2. Is it an error or a hang or a crash?
   An error

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

Note that I do not have a D: drive in my system. Also note that I am required to use thick mode because the current version of oracledb does not support NNE.

Error: internal error in file D:\git_files\node-oracledb\src\njsDbObject.c, line 795 (no error message)
    at DbObject._setAttrValue (C:\Development\Git\ps\ps\node_modules\oracledb\lib\dbObject.js:107:16)
    at DbObject.<anonymous> (C:\Development\Git\ps\ps\node_modules\oracledb\lib\dbObject.js:467:19)
    at DbObject.set (C:\Development\Git\ps\ps\node_modules\oracledb\lib\connection.js:125:18)
    at Function.assign (<anonymous>)
    at new DbObject (C:\Development\Git\ps\ps\node_modules\oracledb\lib\connection.js:99:16)
    at Object.transformValueIn (C:\Development\Git\ps\ps\node_modules\oracledb\lib\transformer.js:144:13)
    at Connection._processBindValue (C:\Development\Git\ps\ps\node_modules\oracledb\lib\connection.js:309:37)
    at Connection._processExecuteBind (C:\Development\Git\ps\ps\node_modules\oracledb\lib\connection.js:346:18)
    at async Connection._processExecuteBinds (C:\Development\Git\ps\ps\node_modules\oracledb\lib\connection.js:395:9)
    at async Connection.execute (C:\Development\Git\ps\ps\node_modules\oracledb\lib\connection.js:894:17) {
  code: 'internal error in file D'
}

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

const oracledb = require('oracledb');
oracledb.initOracleClient({});
console.log('oracledb version: ' + oracledb.versionString);
console.log('oracledb thin mode: ' + oracledb.thin);
if (!oracledb.thin) {
    console.log('oracle client version: ' + oracledb.oracleClientVersionString);
}

executeSql().then(() => console.log('Done'));

async function executeSql() {
    let pool = null, connection = null;
    try {
        pool = await oracledb.createPool({
            user: '',
            password: '',
            connectString: ''
        });

        const num = 589508999999999999999n;
        connection = await pool.getConnection();
        await connection.execute('create or replace type MY_TYPE as object (TESTNUMBER number)');
        let rs = await connection.execute(`declare myType MY_TYPE := :t; begin :t := myType; end;`, {
            t: {
                dir: oracledb.BIND_INOUT,
                type: 'MY_TYPE',
                val: {'TESTNUMBER': num}
            }
        }, {outFormat: oracledb.OUT_FORMAT_OBJECT});
        console.log(JSON.stringify(rs.outBinds));
    } catch (e) {
        console.log(e);
    } finally {
        if (connection) {
            await connection.execute('drop type MY_TYPE');
            await connection.close();
        }
        if (pool) {
            await pool.close(10);
        }
    }
}

My own analysis:
The script executes successfully if I remove the n from 589508999999999999999n, albeit the number is rounded which is what I'm trying to avoid by using a BigInt. The script also executes successfully in thin mode without modification.

It is my understanding that this functionality should work with oracledb 6.7.0 thick mode, please correct me if I'm wrong.

Thank you for your help.

[sharadraju]

Thank you for reporting this. We will look into it.

@adrianvpymnt In the meantime, is it working with Thin mode?

[sudarshan12s]

Hi @adrianvpymnt , we did add support for sql binds for BigInt (typehandlers are used to convert to BigInt from the string returned) . For setting object attributes, it is not yet supported. We shall add this as an enhancement for both thin and thick modes .

As a workaround, I think for now we need to use VARCHAR2 for TESTNUMBER, if it works for you.

await connection.execute('create or replace type MY_TYPE as object (TESTNUMBER VARCHAR2(1024))');
    const rs = await connection.execute(`declare myType MY_TYPE := :t; begin :t := myType; end;`, {
      t: {
        dir: oracledb.BIND_INOUT,
        type: 'MY_TYPE',
        val: {'TESTNUMBER': num.toString()}
      }
    }, {outFormat: oracledb.OUT_FORMAT_OBJECT});

[adrianvpymnt]

This does actually work in thin mode, I mentioned that in my original post, that's why I thought it was a bug that it doesn't work in thick mode. You can verify that yourself by executing my script after commenting out the following line: oracledb.initOracleClient({});

[sudarshan12s]

This does actually work in thin mode, I mentioned that in my original post, that's why I thought it was a bug that it doesn't work in thick mode. You can verify that yourself by executing my script after commenting out the following line: oracledb.initOracleClient({});

I may be missing some code. I tried the above program , it gives the following output, where the value is truncated. Can you confirm if value 589508999999999999999n is returned properly in your case?

oracledb version: 6.7.0
oracledb thin mode: true
{"t":{"TESTNUMBER":589509000000000000000}}
Done

[adrianvpymnt]

I'm sure you'll get the expected result if you add oracledb.fetchAsString = [ oracledb.NUMBER ]; (or some equivalent for BigInt) to the top of the script. Looks like thin mode does not actually work with BigInt properly, my only point was that thin mode did not throw an error. But yea I will need this feature to be implemented at some point in the near future.

[sudarshan12s]

Thanks for confirmation. Yes we will address this issue in thick mode and thin mode as well and provide an update.

Please see the reasons for the above behaviour:

Thick mode currently doesn't not handle JS type BigInt in C layer. Hence it throws an error when BigInt is passed from JS to C.

On thin mode, it was truncating the value (parseFloat) read.