search

oracle / node-oracledb

Oracle Database driver for Node.js maintained by Oracle Corp.
http://oracle.github.io/node-oracledb/
Other
2.24k stars 1.07k forks source link

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 #1565

Closed andersonzapaterra closed 10 months ago

andersonzapaterra commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

What version of the database are you using?

john-hall commented 1 year ago

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 commented 1 year ago

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

john-hall commented 1 year ago

No, I've not set that option.

phantomk commented 1 year ago

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 commented 1 year ago

@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.

phantomk commented 1 year ago

@cjbj Which versions of Oracle are supported by the 'thin mode' of node-oracledb 6

sharadraju commented 1 year ago

@phantomk node-oracledb 6.0 Thin mode supports Oracle Database Release 12.1 and later. Please see the following documentation: https://node-oracledb.readthedocs.io/en/latest/user_guide/appendix_a.html#id1

anthony-tuininga commented 1 year ago

@andersonzapaterra, please try this patch. Thanks to @sudarshan12s, we were able to replicate the issue and resolve it:

diff --git a/lib/thin/protocol/messages/withData.js b/lib/thin/protocol/messages/withData.js
index 72302b77..b1af419b 100644
--- a/lib/thin/protocol/messages/withData.js
+++ b/lib/thin/protocol/messages/withData.js
@@ -582,7 +582,7 @@ class MessageWithData extends Message {
   }

   getBitVector(buf, numBytes) {
-    this.bitVector = buf.readBytes(numBytes);
+    this.bitVector = Buffer.from(buf.readBytes(numBytes));
   }

   processBindParams(buf, params) {
sharadraju commented 1 year ago

@john-hall @andersonzapaterra You might want to try the patch suggested by @anthony-tuininga here https://github.com/oracle/node-oracledb/issues/1565#issuecomment-1581446777

sharadraju commented 1 year ago

The patch in lib/thin/protocol/messages/withData.js for this issue is now available, as indicated by the link above. To apply this patch, simply copy this file to your lib folder and replace the older version. This will be incorporated in the 6.0.2 release.

sharadraju commented 1 year ago

This is now available as part of node-oracledb 6.0.2. See the release announcement

john-hall commented 1 year ago

Installed the new version (6.0.2) on my server and still received the following error:

NJS-112: internal error: read integer of length 40 when expecting integer of no more than length 8

OS: Windows Server 2012R2 NodeJS: v18.12.1 Oracle: 12.2.0.1.0

Note that I do NOT receive this error on my DEV environment, which is:

OS: OSX Ventura 13.4 NodeJS: v18.12.1 Oracle: 19c

I also have another server running the exact same versions as the Win2012 server mentioned above and it is NOT seeing the NJS-112 errors with v6.0.2 but is getting the following error:

NJS-111: internal error: read a negative integer when expecting a positive integer

Let me know if you would like me to perform any additional tests.

anthony-tuininga commented 1 year ago

Can you include the full traceback for these errors? Both of those errors (NJS-111 and NJS-112) indicate a lack of synchronization between the client and server in terms of what is being sent by the server. We have noted another instance of this internally and @sharadraju should be able to get that uploaded for you to try soon!

john-hall commented 1 year ago

Unfortunately, now, when trying to reproduce the error to obtain a stack trace, I am receiving the following error:

ORA-03106: fatal two-task communication protocol error

This is after rolling back to version 5.5.0, which is verified in console at program startup. Not sure how to clear this, perhaps restart database? I had never seen this error prior to trying v6.0.2.

anthony-tuininga commented 1 year ago

There should be no need to rollback to 5.5.0. You can simply enable thick mode by calling oracledb.initOracleClient().

That said, it would still be useful to get the full traceback for the ORA-3106 as well. Are you dropping/recreating tables, views, etc. when (or just before) this error occurs? Are you using DRCP?

sharadraju commented 1 year ago

I am re-opening this issue as the user is still seeing the issue with 6.0.2

john-hall commented 1 year ago

Not using DRCP. No dropping/recreating tables, this is happening during the driver "execute" function of a PLSQL package function:

BEGIN :ret := ce2.get_ref_list_sql(:fieldId, :parentId); END;

Note that executing this same function with same parameters works fine from other clients (TOAD, SQL Developer).

The stack trace I'm getting only shows my code from the call to the driver execute function on up:

Trace at OracleConnection.executeFunction (C:\CaseEnsemble-2.0\server\database\Connection\OracleConnection.js:40:14) at async OracleObject.executeFunction (C:\CaseEnsemble-2.0\server\database\DatabaseEntity\DatabaseEntity.js:148:22) at async OracleObject.getRefListSQL (C:\CaseEnsemble-2.0\server\models\Object\OracleObject.js:504:20) at async OracleObject.getRefListData (C:\CaseEnsemble-2.0\server\models\Object\OracleObject.js:516:17) at async Object.getRefListData (C:\CaseEnsemble-2.0\server\models\Object\object_gql.js:88:29)

Is there a way to enable tracing from within the oracledb driver when using thick mode?

anthony-tuininga commented 1 year ago

Ok. Those two scenarios (DRCP and dropping/recreating tables) were the other issues we discovered when using thin mode. Since this is happening in version 5.5 (thick mode) it clearly is something different. Can you go back to version 6.0.2 and get the traceback with thin mode (much more useful). You can also call oracledb.initOracleClient() in version 6 to use thick mode but that will be about the same as version 5.5 (some code is now in JS but a fair bit has to be in C where tracing is not readily available).

Can you provide some details of your PL/SQL block? What is the return value type and the input parameter value types? How are you calling this function? The error ORA-3106 suggests a mismatch between what the client is sending and the server is expecting (or vice versa).

john-hall commented 1 year ago

Ok, I will work on those suggestions and get back to you ASAP.

john-hall commented 1 year ago

Ok, I can now reproduce the NJS-112 error in my Dev (OSX/19c) environment with version 6.0.2. Here is the stack trace:

  stacktrace: [
    'Error: NJS-112: internal error: read integer of length 40 when expecting integer of no more than length 8',
    '    at Object.throwErr (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/errors.js:591:10)',
    '    at ReadPacket._readInteger (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/thin/protocol/buffer.js:101:14)',
    '    at ReadPacket.readUB8 (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/thin/protocol/buffer.js:501:17)',
    '    at ExecuteMessage.processColumnData (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/thin/protocol/messages/withData.js:431:28)',
    '    at ExecuteMessage.processRowData (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/thin/protocol/messages/withData.js:304:22)',
    '    at ExecuteMessage.processMessage (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/thin/protocol/messages/withData.js:77:12)',
    '    at ExecuteMessage.process (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/thin/protocol/messages/base.js:197:12)',
    '    at ExecuteMessage.process (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/thin/protocol/messages/withData.js:511:11)',
    '    at ExecuteMessage.decode (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/thin/protocol/messages/base.js:188:10)',
    '    at Protocol._decodeMessage (/Users/johnhall/Code/Xerdict/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 (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/thin/protocol/protocol.js:148:9)',
    '    at async ThinConnectionImpl.execute (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/thin/connection.js:796:5)',
    '    at async Connection.execute (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/connection.js:861:16)',
    '    at async Connection.<anonymous> (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/node_modules/oracledb/lib/util.js:162:14)',
    '    at async OracleConnection.executeFunction (/Users/johnhall/Code/Xerdict/CaseEnsemble-2.0/server/database/Connection/OracleConnection.js:35:20)'
  ]

Here are the particulars of the execute function call:

sql: 'BEGIN :ret := ce2.get_ref_list_sql(:fieldId, :parentId); END;', variables: { ret: { dir: 3003, type: [DbType DB_TYPE_VARCHAR], maxSize: 1000000000 }, fieldId: { dir: 3001, val: 169, type: [DbType DB_TYPE_NUMBER] }, parentId: { dir: 3001, val: 151043, type: [DbType DB_TYPE_NUMBER] } }, options: {}

The PLSQL package function takes two numbers as input and returns a VARCHAR2.

john-hall commented 1 year ago

Update: version 6.0.2 in "thick" mode is working with no issues.

anthony-tuininga commented 1 year ago

Can you share the output when you set the environment variable NODE_ORACLEDB_DEBUG_PACKETS to any value? The only part that matters is the part where the PL/SQL block is executed and the response from the database is returned.

One thing that stands out in your invocation is the maxSize of 1 billion! Since PL/SQL doesn't actually support strings of that length, internally the value is converted to a temporary CLOB and passed that way. So does your PL/SQL procedure actually return a CLOB? If it returns a string you can set the maxSize to 32767 (the maximum value allowed) to avoid that internal conversion. If it does return a CLOB, can you try using that data type instead?

Can you also find out if a standalone script that invokes this PL/SQL procedure also causes the problem independently of your application?

anthony-tuininga commented 1 year ago

Can you also share the definition for the PL/SQL function?

john-hall commented 1 year ago 
2023-05-02 18:34:52.618 Sending packet:
0000 : 00 00 00 C7 06 00 00 00 |........|
0008 : 00 00 03 5E 2B 02 04 29 |...^+..)|
0016 : 00 01 01 3D 01 01 0D 00 |...=....|
0024 : 00 00 01 01 04 7F FF FF |........|
0032 : FF 01 01 03 00 00 00 00 |........|
0040 : 00 00 00 00 00 01 00 00 |........|
0048 : 00 00 00 00 00 00 00 00 |........|
0056 : 00 00 00 00 00 3D 42 45 |.....=BE|
0064 : 47 49 4E 20 3A 72 65 74 |GIN.:ret|
0072 : 20 3A 3D 20 63 65 32 2E |.:=.ce2.|
0080 : 67 65 74 5F 72 65 66 5F |get_ref_|
0088 : 6C 69 73 74 5F 73 71 6C |list_sql|
0096 : 28 3A 66 69 65 6C 64 49 |(:fieldI|
0104 : 64 2C 20 3A 70 61 72 65 |d,.:pare|
0112 : 6E 74 49 64 29 3B 20 45 |ntId);.E|
0120 : 4E 44 3B 01 01 01 01 00 |ND;.....|
0128 : 00 00 00 00 00 00 02 80 |........|
0136 : 00 00 00 00 70 01 00 00 |....p...|
0144 : 01 70 00 04 02 00 00 00 |.p......|
0152 : 00 00 02 03 69 01 00 00 |....i...|
0160 : 02 01 00 00 01 16 00 00 |........|
0168 : 00 00 00 00 00 00 02 01 |........|
0176 : 00 00 01 16 00 00 00 00 |........|
0184 : 00 00 00 00 07 00 03 C2 |........|
0192 : 1D 13 04 C3 11 22 2B    |....."+ |

2023-05-02 18:34:52.625 Receiving packet:
0000 : 00 00 00 82 06 00 00 00 |........|
0008 : 00 00 0B 05 01 03 00 01 |........|
0016 : 01 00 00 00 10 20 20 07 |........|
0024 : 01 28 28 00 26 00 01 82 |.((.&...|
0032 : 08 00 03 00 01 7A A9 00 |.....z..|
0040 : 00 00 08 00 00 00 0D 00 |........|
0048 : B2 00 0A 00 00 00 01 00 |........|
0056 : 00 00 00 00 00 00 00 00 |........|
0064 : 00 00 00 00 08 01 06 04 |........|
0072 : 01 0B C0 A5 00 01 17 00 |........|
0080 : 00 00 00 00 00 04 01 01 |........|
0088 : 02 04 75 01 01 00 00 00 |..u.....|
0096 : 01 17 00 2F 00 00 00 00 |.../....|
0104 : 00 03 01 1F B7 01 07 00 |........|
0112 : 02 16 84 01 1B 00 00 2B |.......+|
0120 : 00 01 01 00 00 00 00 00 |........|
0128 : 01 01                   |..      |

2023-05-02 18:34:52.626 Sending packet:
0000 : 00 00 00 0B 0C 00 00 00 |........|
0008 : 01 00 03                |...     |

2023-05-02 18:34:52.626 Sending packet:
0000 : 00 00 00 0B 0C 00 00 00 |........|
0008 : 01 00 02                |...     |

2023-05-02 18:34:52.627 Receiving packet:
0000 : 00 00 00 0B 0C 20 00 00 |........|
0008 : 01 00 02                |...     |

2023-05-02 18:34:52.627 Receiving packet:
0000 : 00 00 00 71 06 00 00 00 |...q....|
0008 : 00 00 04 01 01 02 04 75 |.......u|
0016 : 01 01 02 03 F5 00 00 00 |........|
0024 : 00 2F 00 00 00 00 00 03 |./......|
0032 : 01 1F B7 01 07 00 02 16 |........|
0040 : 84 01 1B 00 00 2B 00 01 |.....+..|
0048 : 01 00 00 00 00 02 03 F5 |........|
0056 : 01 01 36 4F 52 41 2D 30 |..6ORA-0|
0064 : 31 30 31 33 3A 20 75 73 |1013:.us|
0072 : 65 72 20 72 65 71 75 65 |er.reque|
0080 : 73 74 65 64 20 63 61 6E |sted.can|
0088 : 63 65 6C 20 6F 66 20 63 |cel.of.c|
0096 : 75 72 72 65 6E 74 20 6F |urrent.o|
0104 : 70 65 72 61 74 69 6F 6E |peration|
0112 : 0A                      |.       |
john-hall commented 1 year ago

I think the culprit might have been that maxSize value being passed in. I changed it to 32767 and the error stopped happening for that call. Thankfully, that value was defined as a constant and imported when retrieving CLOB or large VARCHAR2 return values. I still have to review all my uses of that constant and make sure that calls that actually return CLOB's have the correct data type specified. But it looks like that solved the problem for me.

anthony-tuininga commented 1 year ago

That's good to know! Can you share the PL/SQL function definition? I can then try to replicate that here and resolve the problem properly. Its good to hear you have a workaround, though! Thanks for the packet output. That will be helpful, too.

john-hall commented 1 year ago

Sure, here's the function definition:

    FUNCTION get_ref_list_sql(
        in_field_id IN  NUMBER,
        in_parent_id IN NUMBER
    ) RETURN VARCHAR2;
anthony-tuininga commented 1 year ago

Thanks. I tried that and returned a small string without difficulty -- but it may be related to the database version as well.

sudarshan12s commented 1 year ago

@john-hall , can you confirm if your application is using callTimeout (conn.callTimeout = 5; // 5msec ) . If its set and you increase it, do you still see the issue?

john-hall commented 1 year ago

My application is not setting callTimeout. Changing the maxSize parameter from 1000000000 to 32767 for the PLSQL function return value fixed the issue for me. What is the default value for callTimeout?

anthony-tuininga commented 1 year ago

The default value is 0 meaning no timeout occurs. Looking at the call stack you provided was the source of this confusion (since the line numbers have changed since 6.0.2 was released). Apologies for the confusion!

anthony-tuininga commented 1 year ago

I tried with an older database just now and it doesn't run into any problems. What is the length of the value that is actually returned?

john-hall commented 1 year ago

The return values can vary from around 400 to approx. 1500... so definitely within max VARCHAR2 (32767) range.

john-hall commented 1 year ago

Update: I've modified our maxStringSize constant to 32767 and added a maxClobSize constant based on Oracle docs:

maxStringSize = 32767;
maxClobSize = 2147483647;

I also updated our app to use the correct constant based on the return data type of each PLSQL package function being called (there are hundreds!). Note: this Node application is a replacement for a PLSQL-based web application that used the now depricated OHS web toolkit (mod_plsql). I've commented out the oracledb.initOracleClient() line and executed our entire test suite in "thin" mode with no errors! Thank you VERY much for your patience in helping resolve this issue! It will be a great advantage to run out app in thin mode so we won't have to install the Oracle Client libraries in the host environement anymore!

john-hall commented 1 year ago

With further thin mode testing, I'm now encountering the following error:

    NJS-111: internal error: read a negative integer when expecting a positive integer

      at Object.throwErr (node_modules/oracledb/lib/errors.js:591:10)
      at ReadPacket._readInteger (node_modules/oracledb/lib/thin/protocol/buffer.js:95:16)
      at ReadPacket.readUB4 (node_modules/oracledb/lib/thin/protocol/buffer.js:492:17)
      at ExecuteMessage.processColumnData (node_modules/oracledb/lib/thin/protocol/messages/withData.js:428:24)
      at ExecuteMessage.processRowData (node_modules/oracledb/lib/thin/protocol/messages/withData.js:304:22)
      at ExecuteMessage.processMessage (node_modules/oracledb/lib/thin/protocol/messages/withData.js:77:12)
      at ExecuteMessage.process (node_modules/oracledb/lib/thin/protocol/messages/base.js:197:12)
      at ExecuteMessage.process (node_modules/oracledb/lib/thin/protocol/messages/withData.js:511:11)
      at ExecuteMessage.decode (node_modules/oracledb/lib/thin/protocol/messages/base.js:188:10)
      at Protocol._decodeMessage (node_modules/oracledb/lib/thin/protocol/protocol.js:77:17)
      at Protocol._processMessage (node_modules/oracledb/lib/thin/protocol/protocol.js:148:9)
      at ThinConnectionImpl.execute (node_modules/oracledb/lib/thin/connection.js:796:5)
      at Connection.execute (node_modules/oracledb/lib/connection.js:861:16)
      at Connection.<anonymous> (node_modules/oracledb/lib/util.js:162:14)
      at OracleConnection.executeQuery (database/Connection/OracleConnection.js:18:22)
      at OracleObjectType.executeQuery (database/DatabaseEntity/DatabaseEntity.js:118:23)
      at OracleObjectType.getUserActions (models/ObjectType/ObjectType.js:33:25)
      at OracleObjectType.getObjectType (models/ObjectType/ObjectType.js:43:30)
      at Object.<anonymous> (models/ObjectType/OracleObjectType.test.js:70:24)

Here is the SQL that produced the error:

        SELECT JSON_ARRAYAGG (
          JSON_OBJECT(
            'id' VALUE action_id,
            'name' VALUE action_name,
            'type' VALUE (
              SELECT JSON_OBJECT (
                'id' VALUE action_type_id,
                'name' VALUE action_type_name,
                'active' VALUE active
              )
              FROM tb_document_action_type
              WHERE action_type_id = da.action_type
            ),
            'destinationState' VALUE (
              SELECT JSON_OBJECT (
                'id' VALUE state_id,
                'name' VALUE state_name,
                'active' VALUE active
              )
              FROM tb_document_state
              WHERE state_id = da.destination_state
            ),
            'finalizationHook' VALUE finalization_hook,
            'allowOwner' VALUE allow_owner,
            'active' VALUE active,
            'notifyOwner' VALUE notify_owner
          )
          ORDER BY action_name
          RETURNING CLOB
        )
        FROM
          tb_document_action da, tb_document_action_role dar, tb_user_role ur
        WHERE 
          dar.role = ur.role
          AND dar.action = da.action_id
          AND da.document_type = :id
          AND ur.user_id = :user_id

Let me know if I should open a separate issue for this.

anthony-tuininga commented 1 year ago

Yes, please do open another issue with this information. How many rows are being returned? Please post the packet output on the new issue, too. Looks like this may be LOB related, too! If you are able to demonstrate the problem with a standalone test case that would be ideal.

john-hall commented 1 year ago

Ok, will do. Thanks. Having a hard time reproducing this one... seems to be intermittent and goes away when I turn on the packet tracing. This makes me think it may be timing related. I'll submit a new issue if I can reproduce in a consistent way and get a packet trace.

anthony-tuininga commented 1 year ago

Update: I've modified our maxStringSize constant to 32767 and added a maxClobSize constant based on Oracle docs:

maxStringSize = 32767;
maxClobSize = 2147483647;

I also updated our app to use the correct constant based on the return data type of each PLSQL package function being called (there are hundreds!). Note: this Node application is a replacement for a PLSQL-based web application that used the now depricated OHS web toolkit (mod_plsql). I've commented out the oracledb.initOracleClient() line and executed our entire test suite in "thin" mode with no errors! Thank you VERY much for your patience in helping resolve this issue! It will be a great advantage to run out app in thin mode so we won't have to install the Oracle Client libraries in the host environement anymore!

Regardng this issue, I can verify that there is a problem with how the client is interpreting the LOB values that are being returned when you set maxSize > 32767. We are continuing to investigate this but your solution is the correct one regardless!

john-hall commented 1 year ago

Thanks Anthony. I have reverted to using v6.0.2 in "thick" mode because the NJS-111 error is very sporadic and difficult to reproduce consistently... but it continues to happen in our application. I will continue to monitor the issues/releases and try to contribute whenever possible.

anthony-tuininga commented 1 year ago

I understand. Hopefully the issue above is something we can figure out and resolve and you can try your application again after that.

sudarshan12s commented 1 year ago

@john-hall , Can you provide a standalone inserting some rows and return it from pl/sql function. I started something like below to capture the specifics and tried multiple changes to it but could not reproduce. . Please modify/fill as you notice specific to your case.. Thanks.

'use strict';

const oracledb = require('oracledb');

const proc = `create or replace function issue_1565_fn (inval1 number, inval2 number) return varchar2
is
data VARCHAR2(3276);
begin
select clob1 into data from lobPrefetchT where id = 1;
return data;
end;
`;

Error.stackTraceLimit = 50;

const config = {
  user: process.env.NODE_ORACLEDB_USER,
  password: process.env.NODE_ORACLEDB_PASSWORD,
  connectString: process.env.NODE_ORACLEDB_CONNSTRING
};

const createTable = `create table lobPrefetchT (id NUMBER, clob1 VARCHAR2(3276), clob2 VARCHAR2(3276))`;
const dropTable = `drop table lobPrefetchT purge`;
const dropProc = `drop function issue_1565_fn`;
const insertsql = `insert into lobPrefetchT values(:1,:2,:3)`;

async function run() {

  const connection = await oracledb.getConnection(config);

  try {
    await connection.execute(dropProc);
    await connection.execute(dropTable);
  } catch (err) {
    console.log(" Ignore cleanup ", err);
  }

  try {
    await connection.execute(proc);
    await connection.execute(createTable);
    const dataLen = 3276;
    await connection.execute(insertsql, [1, '1'.repeat(dataLen), '2'.repeat(dataLen)]);
    const binds = {
      ret: {
        dir: oracledb.BIND_OUT,
        type: oracledb.DB_TYPE_VARCHAR,
        maxSize: 1000000000
      },
      fieldId: 169,
      parentId: 151043
    };
    const sql = "begin :ret := issue_1565_fn(:fieldId, :parentId); end;";
    const result1 = await connection.execute(sql, binds);
    console.log("result (1):", result1);
    const result2 = await connection.execute(sql, binds);
    console.log("result (2):", result2);
  } catch (err) {
    console.log(" Failed with Error ", err);
  } finally {
    await connection.close();
  }
}

run();
sharadraju commented 12 months ago

@john-hall node-oracledb 6.0.3 has been released. Please try the use case with the patch.

ertl commented 11 months ago

@sharadraju: I encounter this error message with version 6.0.3

NJS-112: internal error: read integer of length 31 when expecting integer of no more than length 4 at position 313 of packet 210

No Problems with the thick client though