NLS_DATE_FORMAT not working on windows

[owendswang]

1. What versions are you using? Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

   > process.platform
   'win32'
   > process.version
   'v18.17.0'
   > process.arch
   'x64'
   > require('oracledb').versionString
   '6.2.0'
   > require('oracledb').oracleClientVersionString
   undefined
   (Actually instant client 12.2)

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

3. What error(s) or behavior you are seeing? When setting NLS_LANG, ORA-00933 appears. Without it, the NLS_DATE_FORMAT is not working. But in Ubuntu, NLS_DATE_FORMAT is working and the NLS_LANG setting would cause the same problem ORA-00933.

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

   
   const { existsSync } = require('fs');
   const oracledb = require('oracledb');

function initSession(connection, requestedTag, callbackFn) { connection.clientId = "ODSWApp"; connection.execute( alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF' NLS_LANGUAGE='AMERICAN' NLS_LANG='AMERICAN_AMERICA.AL32UTF8' TIME_ZONE='Asia/Shanghai', callbackFn); }

const config = { user: 'test', password: 'test', connectString: 'localhost:1521/orclpdb1', sessionCallback: initSession, }

const libPath = "M:\instantclient_12_2";

if (libPath && existsSync(libPath)) { oracledb.initOracleClient({ libDir: libPath }); }

oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT; oracledb.autoCommit = true; oracledb.fetchAsString = [oracledb.DATE]; oracledb.extendedMetaData = true;

class OracleConnectionPool { async init() { oracledb.createPool(config); try { await oracledb.createPool(config); } catch (err) { console.error('init() error: ' + err.message); } }

async closePoolAndExit() { try { await oracledb.getPool().close(2); // console.log('Pool closed'); process.exit(0); } catch (err) { console.error(err.message); process.exit(1); } } }

const pool = new OracleConnectionPool(); pool.init();

let sql = 'select sysdate from dual'; let binds = []; try { const connection = await oracledb.getConnection(); let result = await connection.execute(sql, binds); console.log(result); } catch (err) { console.log(err); }

pool.closePoolAndExit();

[sharadraju]

Thanks for using node-oracledb. node-oracledb 6.0 has two modes by default - the 'Thin' and 'Thick' modes. The Thin mode is the default mode implemented purely in JavaScript and the Thick mode uses Oracle Client libraries like the previous versions. initOracleClient() call needs to be executed for Thick mode to be enabled.

In your case, it seems like the initOracleClient() call may not have executed. Can you check if the instant client libraries are being picked by your app? Also was it working with earlier node-oracledb versions?

[owendswang]

Thansk for the explanation. I understand it now. So the code use 'oracledb.initOracleClient()' any way to reach my goal.

if (libPath && existsSync(libPath)) {
  oracledb.initOracleClient({ libDir: libPath });
} else {
  oracledb.initOracleClient();
}