Timezone Region Not Found (Oracle 11g)

1
votes

Does anyone know why the first query would be causing this error to be thrown while the second one works?

ORA-01882: timezone region not found 01882. 00000 - "timezone region %s not found"

  1. Causes Error: SELECT SYSTIMESTAMP AT TIME ZONE (SELECT t.TIME_ZONE FROM SOME_TABLE t WHERE t.TIME_ZONE = 'America/Denver' AND ROWNUM = 1) FROM DUAL

  2. Works Correctly: SELECT SYSTIMESTAMP AT TIME ZONE (SELECT 'America/Denver' FROM SOME_TABLE t WHERE ROWNUM = 1) FROM DUAL

Note: This is running on a Oracle Database 11g Release 11.2.0.4.0 - 64bit db. I've verified both queries work correctly on another db with the same db version. Not sure what else could be causing this.

1
oracleoracle11g
Weird. What's the data type of SOME_TABLE.TIME_ZONE? Is it just a varchar2? – kfinity
Ah, I think that's the issue. The db with the error has a data type of NVARCHAR2 ( vs VARCHAR2 in the working db ) – angrycrab
Yup, casting to VARCHAR2 fixed it. Thanks for the pointer! SELECT SYSTIMESTAMP AT TIME ZONE (SELECT CAST(t.TIME_ZONE AS VARCHAR2(80)) FROM SOME_TABLE t WHERE t.TIME_ZONE = 'America/Denver' AND ROWNUM = 1) FROM DUAL – angrycrab

1 Answers

1
votes

To summarize the root cause, it was related to t.TIME_ZONE's data type (which was NVARCHAR2). Here's an example showing that NVARCHAR2 time zone names are not supported in 11g:

Does not work: SELECT SYSTIMESTAMP AT TIME ZONE CAST( 'America/Denver' as NVARCHAR2(80)) FROM DUAL

Works: SELECT SYSTIMESTAMP AT TIME ZONE CAST( 'America/Denver' as VARCHAR2(80)) FROM DUAL

Wrapping t.TIME_ZONE in TO_CHAR() fixed the problem.