Error updating CLOB w/ UTF8 charset

[xiongtx]

1. What versions are you using?

Oracle 23.9.0.0, thin client.

process.platform: darwin
process.version: v24.2.0
process.arch: arm64
oracledb.versionString: 6.9.0
oracledb.oracleClientVersionString: undefined

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

Error

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

When DB has charset UTF8 (instead of default AL32UTF8), UPDATE to CLOB column when it's not the only field being bound results in error:

ORA-01461: The value at bind position 2 exceeded the maximum VARCHAR2 length.

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

See https://github.com/xiongtx/node-oracledb-clob-bug.

[xiongtx]

This seems remarkably similar to this issue in Apache Derby, down to the 10,923 threshold.

[sudarshan12s]

Thanks @xiongtx for reporting the issue. I could not verify with DB charset UTF8(Unicode's CESU-8 with Unicode 3.0 repertiore of characters) . Can you confirm if this issue is seen on thick mode also by adding oracledb.initOracleClient

“AL32UTF8 is the recommended database character set for all new Oracle Database deployments. Was the Docker image (container-registry.oracle.com/database/free:23.9.0.0) default to UTF8 or is it changed ?”

[xiongtx]

I am not using the thick client. Did you follow the README in my repo?

The default charset is AL32UTF8, yes. Our DB instance, being old, is still UTF8. You need to follow the instructions to change charset to UTF8.

[sudarshan12s]

I am not using the thick client. Did you follow the README in my repo?

The default charset is AL32UTF8, yes. Our DB instance, being old, is still UTF8. You need to follow the instructions to change charset to UTF8.

Thank you, I can see that the conversion was done explicitly. We will review it and get back.

[sudarshan12s]

Hi @xiongtx ,
just to update on this: the fix is under review.

In the meantime, can you try the following workaround to see if it works. pass CLOB type explicitly.

const result = await connection.execute(
          `UPDATE clob_test SET data = :data WHERE id = :id`,
          { data: { val: data, type: oracledb.CLOB }, id: testId },
          { autoCommit: true }
        );

[xiongtx]

I had to use {data: {type: oracledb.CLOB, val: data}}, w/out the extra val, but yes, it works.

[sudarshan12s]

I had to use {data: {type: oracledb.CLOB, val: data}}, w/out the extra val, but yes, it works.

Thanks. It was a typo (corrected now ) . your syntax is correct.