Scandic characters in bind variables cause error ORA-01460

[ktj]

1. What versions are you using?
   oracle version 19c

   process.platform
   darwin
   process.version
   v18.16.0
   process.arch
   arm64
   require('oracledb').versionString
   6.0.0

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

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

Error: ORA-01460: unimplemented or unreasonable conversion requested

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

const oracledb = require('oracledb')
const dbConfig = {
  user: process.env.NODE_ORACLEDB_USER,
  password: process.env.NODE_ORACLEDB_PASSWORD,
  connectString: process.env.NODE_ORACLEDB_CONNECTIONSTRING,
}

async function run() {
  let connection

  try {
    let sql, binds, options, result

    connection = await oracledb.getConnection(dbConfig)

    const stmts = [
      `DROP TABLE no_example`,

      `CREATE TABLE no_example (id NUMBER, data VARCHAR2(20))`,
    ]

    for (const s of stmts) {
      try {
        await connection.execute(s)
      } catch (e) {
        if (e.errorNum != 942) console.error(e)
      }
    }

    sql = `INSERT INTO no_example VALUES (:1, :2)`

    binds = [
      [101, 'Alpha'],
      [102, 'Beta'],
      [103, 'Gamma'],
    ]

    // For a complete list of options see the documentation.
    options = {
      autoCommit: true,
      // batchErrors: true,  // continue processing even if there are data errors
      bindDefs: [
        { type: oracledb.NUMBER },
        { type: oracledb.STRING, maxSize: 20 },
      ],
    }

    result = await connection.executeMany(sql, binds, options)

    console.log('Number of rows inserted:', result.rowsAffected)

    //
    // Query the data
    //

    sql = `SELECT * FROM no_example WHERE data=:variable`

    binds = { variable: 'ä' }

    // For a complete list of options see the documentation.
    options = {
      outFormat: oracledb.OUT_FORMAT_OBJECT, // query result format
      // extendedMetaData: true,               // get extra metadata
      // fetchArraySize:   100                 // internal buffer allocation size for tuning
    }

    result = await connection.execute(sql, binds, options)

    // Column metadata can be shown, if desired
    // console.log("Metadata: ");
    // console.dir(result.metaData, { depth: null });

    console.log('Query results: ')
    console.dir(result.rows, { depth: null })
  } catch (err) {
    console.error(err)
  } finally {
    if (connection) {
      try {
        await connection.close()
      } catch (err) {
        console.error(err)
      }
    }
  }
}

run()

Run with command NODE_ORACLEDB_USER=SYSTEM NODE_ORACLEDB_PASSWORD=oracle NODE_ORACLEDB_CONNECTIONSTRING=local.oracle.com:1521/xe node test.js

If I change this row binds = { variable: 'ä' } to binds = { variable: 'a' }, then it works without a problem

This problem didn't exist in version 5.5.0.

[anthony-tuininga]

I can replicate this issue and I also know the solution. :-) The problem is that the length of the value in characters doesn't match the length of the value in bytes. This patch resolves the issue:

--- a/lib/transformer.js
+++ b/lib/transformer.js
@@ -152,13 +152,16 @@ function transformValueIn(info, value, options) {
       types.DB_TYPE_NCLOB);
     if (info.type !== types.DB_TYPE_CLOB &&
         info.type !== types.DB_TYPE_NCLOB &&
-        info.cqn === undefined &&
-        (info.maxSize === undefined || value.length > info.maxSize)) {
-      if (info.checkSize) {
-        errors.throwErr(errors.ERR_MAX_SIZE_TOO_SMALL, info.maxSize,
-          value.length, options.pos);
+        info.cqn === undefined) {
+      const valueLen = Buffer.byteLength(value);
+      if (info.maxSize === undefined || valueLen > info.maxSize) {
+        if (info.checkSize) {
+          errors.throwErr(errors.ERR_MAX_SIZE_TOO_SMALL, info.maxSize,
+            value.length, options.pos);
+        }
+        info.maxSize = valueLen;
       }
-      info.maxSize = value.length;
     }
     return value;

Some more testing is needed but something like this patch will be included in the next release. Thanks for reporting this!

[jameschenjav]

I encountered similar issue with unicode char U+00A0. It was working with NLS_LANG=".AL32UTF8". Since the Thin mode does not follow NLS env vars, I wonder how is it supposed to work? Because JS encodes everything as UTF-16, so it will be the same as AL32UTF8? What if I have a non-unicode server setting?

btw, I have tried the patch above, it does work.

[anthony-tuininga]

Thin mode uses AL32UTF8 exclusively for communication with the server. If the server is not using AL32UTF8 itself (although that has been the default value for some years now) then the server performs any necessary conversion. What JavaScript uses internally isn't particularly relevant -- the characters in whatever string you supply are encoded to UTF-8 before being sent to the server. The problem was that the code wasn't looking at the length in encoded bytes.

I'm happy to hear that the patch works for you like it does for me!

[jameschenjav]

Thin mode uses AL32UTF8 exclusively for communication with the server. If the server is not using AL32UTF8 itself (although that has been the default value for some years now) then the server performs any necessary conversion. What JavaScript uses internally isn't particularly relevant -- the characters in whatever string you supply are encoded to UTF-8 before being sent to the server. The problem was that the code wasn't looking at the length in encoded bytes.

I'm happy to hear that the patch works for you like it does for me!

thanks a lot!

[jameschenjav]

Hi @anthony-tuininga
I have another question about Thin mode. Previously, I was providing both DB_ORACLE_POOL_SIZE and UV_THREADPOOL_SIZE in order to maximize the connections. My understanding is Instant Client would hold a thread until a request finished.

Does it mean, I no longer need to set both DB_ORACLE_POOL_SIZE and UV_THREADPOOL_SIZE in Thin mode, and there are just normal (TCP?) requests that will be handled by libuv directly?

[cjbj]

[Update] I forked this question to a new issue - let's follow up there.

[sharadraju]

The patch in lib/transformer.js for this issue is now available. 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.1 release.

[sharadraju]

Closing this issue as fixed in 6.0.1 release