node-oracledb charset CLOB bug

There is a bug w/ node-oracledb when inserting CLOB and binding more than just the CLOB parameter in a DB w/ UTF8 charset.

Start the image

docker-compose up -d

Connect to PDB

Drop into the container:

docker exec -it <container ID> sh

Connect w/ sqlplus:

sqlplus sys/oracle as sysdba

Switch to right PDB:

ALTER SESSION SET CONTAINER = FREEPDB1;

Change charset

SHUTDOWN IMMEDIATE;

STARTUP RESTRICT;

ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;

SHUTDOWN IMMEDIATE;

STARTUP;

Check the charset:

SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';

VALUE
----------------------------------------------------------------
UTF8

Run test script

npm install

node test-clob.js

Expected output:

$ node test-clob.js
Connected to Oracle Database
Creating clob_test table if it doesn't exist...
Table created
Truncating clob_test table...
Table truncated
Inserting initial data with ID = test1...
Insert completed

[Length 10922] Case 1: Only binding data
Update completed successfully. Rows affected: 1

[Length 10922] Case 2: Binding data and id
Update completed successfully. Rows affected: 1

[Length 10923] Case 1: Only binding data
Update completed successfully. Rows affected: 1

[Length 10923] Case 2: Binding data and id
Update failed with error:
Message: ORA-01461: The value at bind position 2 exceeded the maximum VARCHAR2 length.
Help: https://docs.oracle.com/error-help/db/ora-01461/
Error code: 1461
Stack: Error: ORA-01461: The value at bind position 2 exceeded the maximum VARCHAR2 length.
Help: https://docs.oracle.com/error-help/db/ora-01461/
    at Protocol._processMessage (/Users/tianxiangxiong/github/node-oracledb-clob-bug/node_modules/oracledb/lib/thin/protocol/protocol.js:204:17)
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async ThinConnectionImpl._execute (/Users/tianxiangxiong/github/node-oracledb-clob-bug/node_modules/oracledb/lib/thin/connection.js:224:7)
    at async ThinConnectionImpl.execute (/Users/tianxiangxiong/github/node-oracledb-clob-bug/node_modules/oracledb/lib/thin/connection.js:1075:14)
    at async Connection.execute (/Users/tianxiangxiong/github/node-oracledb-clob-bug/node_modules/oracledb/lib/connection.js:998:16)
    at async Connection.execute (/Users/tianxiangxiong/github/node-oracledb-clob-bug/node_modules/oracledb/lib/util.js:298:16)
    at async run (/Users/tianxiangxiong/github/node-oracledb-clob-bug/test-clob.js:81:24)

[Length 32767] Case 1: Only binding data
Update completed successfully. Rows affected: 1

[Length 32767] Case 2: Binding data and id
Update failed with error:
Message: ORA-01461: The value at bind position 2 exceeded the maximum VARCHAR2 length.
Help: https://docs.oracle.com/error-help/db/ora-01461/
Error code: 1461
Stack: Error: ORA-01461: The value at bind position 2 exceeded the maximum VARCHAR2 length.
Help: https://docs.oracle.com/error-help/db/ora-01461/
    at Protocol._processMessage (/Users/tianxiangxiong/github/node-oracledb-clob-bug/node_modules/oracledb/lib/thin/protocol/protocol.js:204:17)
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async ThinConnectionImpl._execute (/Users/tianxiangxiong/github/node-oracledb-clob-bug/node_modules/oracledb/lib/thin/connection.js:224:7)
    at async ThinConnectionImpl.execute (/Users/tianxiangxiong/github/node-oracledb-clob-bug/node_modules/oracledb/lib/thin/connection.js:1075:14)
    at async Connection.execute (/Users/tianxiangxiong/github/node-oracledb-clob-bug/node_modules/oracledb/lib/connection.js:998:16)
    at async Connection.execute (/Users/tianxiangxiong/github/node-oracledb-clob-bug/node_modules/oracledb/lib/util.js:298:16)
    at async run (/Users/tianxiangxiong/github/node-oracledb-clob-bug/test-clob.js:81:24)

[Length 32768] Case 1: Only binding data
Update completed successfully. Rows affected: 1

[Length 32768] Case 2: Binding data and id
Update completed successfully. Rows affected: 1
Connection closed