Query error when set non default ORA_SDTZ for some dates

[ikdi]

1. What versions are you using?

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production and Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0

Nodejs data

process.platform: linux
process.version: v14.17.3
process.arch: x64
require('oracledb').versionString: 5.2.0
require('oracledb').oracleClientVersionString: 19.6.0.0.0

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

3. What error(s) or behavior you are seeing? (node:171924) UnhandledPromiseRejectionWarning: Error: ORA-01878: specified field not found in datetime or interval

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

process.env.ORA_SDTZ = "Europe/Kiev";

const oracledb = require('oracledb');

const user = 'user';
const password = 'pwd;
const connectString = 'connectString';
const sql =
`with test as (select to_date('29.03.1992','dd.mm.yyyy') dt from dual) select dt from test`;

async function run() {
  const conn = await oracledb.getConnection({ user, password, connectString });
  const result = await conn.execute(sql);
  console.dir(result.rows);
  conn.close();
}

run();

[cjbj]

Tried with SQL*Plus?

[ikdi]

Yes, everything is ok.

SQL> ALTER SESSION SET TIME_ZONE='Europe/Kiev';

Session altered.

SQL> with test as (select to_date('29.03.1992','dd.mm.yyyy') dt from dual) select dt from test;

DT
---------
29-MAR-92

[cjbj]

I can repro :(

[ikdi]

By default nodejs uses Linux system timezone

$ cat /etc/timezone
Europe/Kiev

Maybe this detail can help to reproduce

[ikdi]

According to the documentation Oracle Instant Client must includes timezone_xx.dat. But I don't see this file in distribution and I don't have its. Could its absence be the reason?

[cjbj]

The timezone files are embedded in Instant Client. I'll improve the doc. You can use external, updated files if you need to.

We'll just need to debug your scenario.

[leonidmalashev]

I have reproduced the issue. In the finale, after setting an environment variable ORA_TZFILE=timezone_32.dat then error not appeared.

[cjbj]

@leonidmalashev thanks for the workaround.

cjones@mac:~/n/issues$ node issue1394.js 
[Error: ORA-01878: specified field not found in datetime or interval] {
  errorNum: 1878,
  offset: 0
}
cjones@mac:~/n/issues$ ORA_TZFILE=timezone_32.dat node issue1394.js 
{
  metaData: [ { name: 'DT' } ],
  rows: [ { DT: 1992-03-28T14:00:00.000Z } ]
}
cjones@mac:~/n/issues$ ~/Downloads/instantclient_19_8/genezi -v
Client Shared Library 64-bit - 19.8.0.0.0

System name:	Darwin
Release:	20.5.0
Version:	Darwin Kernel Version 20.5.0: Sat May  8 05:10:33 PDT 2021; root:xnu-7195.121.3~9/RELEASE_X86_64
Machine:	x86_64

TIMEZONE INFORMATION
--------------------
Operating in Instant Client mode.

Small timezone file = /Users/cjones/Downloads/instantclient_19_8/oracore/zoneinfo/timezone_32.dat
Large timezone file = /Users/cjones/Downloads/instantclient_19_8/oracore/zoneinfo/timezlrg_32.dat

@pvenkatraman is following up.

FWIW, the same issue & workaround exists with the v35 timezone files in 21c client.

[ikdi]

thanks @leonidmalashev Works like a charm )

[ikdi]

@cjbj Unfortunately, after using this workaround ( ORA_TZFILE=timezone_32.dat node issue1394.js ) I get other issue. Some dates now are not showed correctly

1. What versions are you using?

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production and Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0

Nodejs data

process.platform: linux
process.version: v16.15.1
process.arch: x64
require('oracledb').versionString: 5.4.0
require('oracledb').oracleClientVersionString: 21.6.0.0.0

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

3. What error(s) or behavior you are seeing? The returning date is not correct

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

index.js

process.env.ORA_SDTZ = "Europe/Kiev";

const oracledb = require('oracledb');

const user = 'user';
const password = 'pwd;
const connectString = 'connectString';
const sql =
`with test as (select to_date('01.02.2022','dd.mm.yyyy') dt from dual) select dt from test`;

async function run() {
  const conn = await oracledb.getConnection({ user, password, connectString });
  const result = await conn.execute(sql);
  console.dir(result);
  conn.close();
}

run();

So now when I run with ORA_TZFILE parameter I get the incorrect date (must be 2022-01-31T22:00:00.000Z)

dmytro@station ~/projects/test/errDate $ ORA_TZFILE=timezone_35.dat node index.js
{
  metaData: [ { name: 'DT' } ],
  rows: [ [ 2022-01-31T21:00:00.000Z ] ]
}

When I run without setting ORA_TZFILE I get the correct date but I have the problem in the first comments

dmytro@station ~/projects/test/errDate $ node index.js
{
  metaData: [ { name: 'DT' } ],
  rows: [ [ 2022-01-31T22:00:00.000Z ] ]
}

[cjbj]

@pvenkatraman it might be time to give the owners of the relevant Oracle code a prod.

[ikdi]

Are there any updates planned for this bug?

[joaomartinssysnovare]

I got the same error on summer/winter time modification.

Example:

process.env.ORA_SDTZ = "Europe/Lisbon";

const oracledb = require('oracledb');

const user = 'user'; const password = 'password'; const connectString = 'connectString'; const sql = with test as (select to_date('26.03.2023','dd.mm.yyyy') dt from dual) select dt from test;

async function run() { const conn = await oracledb.getConnection({ user, password, connectString }); const result = await conn.execute(sql); console.dir(result); conn.close(); }

run();

[anthony-tuininga]

Are you using version 6? And what are the results you are getting?

[ikdi]

@anthony-tuininga Everything is good! I've checked thick and thin versions. Thank you!