Error "NJS-112: internal error: read integer of length 19 when expecting integer of no more than length 4" when performing SELECT on large amount of rows

[andersonzapaterra]

I am encountering an issue when using the node-oracledb library to perform a SELECT query on a large number of rows in my Oracle table. The error I'm getting is:

NJS-112: internal error: read integer of length 19 when expecting integer of no more than length 4

Here are the details:

Environment:

Node.js 18
node-oracledb version: 6.0.0

SQL Query:
I'm executing the following SQL query:

SELECT * FROM PCCLIENT

Note: The error occurs when the query returns a large number of rows. If the query returns only a small number of rows, the error does not occur.

fetchArraySize setting:
I've tested by changing the fetchArraySize setting, but the error still occurs when the number of rows is large.

Attempted resolutions:

I've tried changing the fetchArraySize, but the problem persists.
I've also checked to see if there's enough memory space, and it seems that there is.

code:

const result = await connection.execute("SELECT * FROM PCCLIENT",{},{
            outFormat: oracledb.OUT_FORMAT_OBJECT,   // query result format
            extendedMetaData: false,               // get extra metadata
            fetchArraySize:   150,                 // internal buffer allocation size for tuning
            maxRows:1000
        });

[anthony-tuininga]

Can you define the term "large"? Since you have set the parameter maxRows I presume no more than that amount. Can you provide the definition of the table PCCLIENT? What happens if you try a small fetchArraySize (like 5). Do you still run into the problem then?

[andersonzapaterra]

I changed the value of fetchArraySize to 5, and it worked. My issue was that when fetching a large amount of data from a table, I was getting the NJS-112 error. I thought that increasing the fetchArraySize value would allow me to retrieve more rows from the database. I didn't understand why decreasing the fetchArraySize actually resulted in more data being returned by the database.

You're very welcome. Happy to help!

[anthony-tuininga]

Decreasing the fetchArraySize value was to force the rows to fit within a single packet. It sounds like the logic that handles multiple packets may need some tuning in some edge cases! Since we have not experienced this ourselves are you able to provide the table definition, and if possible, the rows as well? Thanks!

[andersonzapaterra]

I'm sorry, but I can't provide the table definition or the rows because they are confidential. However, I can tell you that the table has almost 800 fields and more than 1700 rows of data.

[anthony-tuininga]

Ok. If you are able to experiment and see if there is a particular fetchArraySize above which it consistently fails for you, that would be something. We'll try to replicate ourselves but that may prove difficult!

[john-hall]

I'm getting this same error all over my application now: NJS-112: internal error: read integer of length 40 when expecting integer of no more than length 8. What's strange is that it's only happening in production, not test, which are supposed to be exactly the same. Also not happening in my dev environment with same tables but different version of oracle. I'm going to include a stack trace in case that would be helpful:

OracleObject.getRefListData Error: NJS-112: internal error: read integer of length 40 when expecting integer of no more than length 8
at Object.throwErr (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\errors.js:588:10)
at ReadPacket._readInteger (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\thin\protocol\buffer.js:100:14)
at ReadPacket.readUB8 (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\thin\protocol\buffer.js:500:17)
at ExecuteMessage.processColumnData (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\thin\protocol\messages\withData.js:430:28)
at ExecuteMessage.processRowData (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\thin\protocol\messages\withData.js:304:22)
at ExecuteMessage.processMessage (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\thin\protocol\messages\withData.js:76:12)
at ExecuteMessage.process (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\thin\protocol\messages\base.js:196:12)
at ExecuteMessage.process (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\thin\protocol\messages\withData.js:508:11)
at ExecuteMessage.decode (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\thin\protocol\messages\base.js:187:10)
at Protocol._decodeMessage (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\thin\protocol\protocol.js:77:17)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async Protocol._processMessage (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\thin\protocol\protocol.js:148:9)
at async ThinConnectionImpl.execute (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\thin\connection.js:793:5)
at async Connection.execute (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\connection.js:858:16)
at async Connection. (C:\CaseEnsemble-2.0\server\node_modules\oracledb\lib\util.js:160:14)
at async OracleConnection.executeFunction (C:\CaseEnsemble-2.0\server\database\Connection\OracleConnection.js:35:20) {
code: 'NJS-112'
}

[anthony-tuininga]

What version of the database are you using?

[john-hall]

12.2 in production and test, 19c in dev. Not seeing the error in dev or test, which is odd since test is 12.2 also.
From v$version: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

[sharadraju]

@john-hall Have you set any fetchArraySize value for the connections?

[john-hall]

No, I've not set that option.

[phantomk]

I've been continuously encountering this error and have never been successful. It happens regardless of whether the table has only one field or is an empty table.
Even when I set fetchArraySize: 1, I still get the same error.

Error: NJS-112: internal error: read integer of length 64 when expecting integer of no more than length 4

I get this error when executing await oracledb.getConnection(dbConfig), not when run a query.

const dbConfig = {
  user: 'sys',
  connectString: '192.168.51.28:1521/ora11g',
  password: '123456',
  outFormat: oracledb.OUT_FORMAT_OBJECT,
}

node: v16.15.1
node oracledb: 6.0.0
oracle: 11g
system: centos7

[cjbj]

@phantomk The default 'thin mode' of node-oracledb 6 won't connect to Oracle DB 11.2. You need to use Thick mode, see https://node-oracledb.readthedocs.io/en/latest/user_guide/initialization.html#enabling-node-oracledb-thick-mode. Our next version will give a better error message for this scenario.