When using Quick Browse function against a table, as well as straight selects, with timezone fields user get the following error message:
ORA-01882: timezone region %s not found
The database is correctly updated to handle timezones - per Metalink Note 415912.1. Sqlplus selects work OK.
This error is a user environment error.
1) User is located in Maryland (MD).
2) Maryland is in the Eastern Time Zone - in Oracle terms 'US/Eastern'
3) When you run query on this view:
SELECT * FROM v$timezone_names where TZNAME = 'US/Eastern'
you find 4 TZABBREV val;ues (LMT,EST,EWT,EDT)
As you can see one US state may use multiple time zones,
4) Run the following query:
SELECT EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '1999-01-01 10:00:00 -08:00') FROM DUAL;
you find result "UNKNOWN"
The ambiguity arises because the time zone numerical offset is provided in the expression, and that numerical offset may map to more than one time zone region.
5) In addition run these two querries (this is a real case example form a user I found on Web)
select TO_TIMESTAMP_TZ('2006-07-01 12:00:00 CET CEST', 'YYYY-MM-DD HH24:MI:SS TZR TZD') from dual;
select TO_TIMESTAMP_TZ('2006-01-01 12:00:00 CET CEST', 'YYYY-MMM-DD HH24:MI:SS TZR TZD') from dual;
NOTES to the above example:
- CET is Central European Timezone
- CEST is Central European Summer Timezone
- pay attention to dates:
- in first query it is '2006-07--1' - Summer Time
- in second it is '2006-01-01' - Winter Time
When running the above querries:
- the first one was OK, because the CET CEST timezone is valid for 2006-07-01
- while the second one was wrong because CET CET timezone was NOT valid for 2006-01-01
6) you can repeat the same exercise for Maryland (US) time zones
Check to make sure the timezone files on the client side are not out of date. If so, please go ahead and update them.