ORA-01882: TIMEZONE REGION NOT FOUND. This error occurs when timezone region string not found.The specified region name was not found.There are a number of time zone constants defined in oracle to use.See the last part of this post to know hoe to get the defined time zone constants in your oracle database.
Here is an example and a solution for the problem:

SQL> select EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '01-01-08 11:53:42 PM +05:30')TIMEZONE_REGION from dual;
select EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '01-01-08 11:53:42 PM +05:30')TIMEZONE_REGION from dual
ORA-01882: timezone region  not found

SQL>

The timezone region string was missing from the above query.
Rewrite the query as follows

SQL> select EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '01-01-08 11:53:42 Pacific/Midway')TIMEZONE_REGION from dual;
TIMEZONE_REGION
Pacific/Midway

SQL>

Now it works!
Also to see the Time Zones try the following query:

SQL> SELECT * from V$TIMEZONE_NAMES;

It will display a number of entries like
TZNAME—————Etc/GMT-6Etc/GMT-7Europe/RigaEurope/SarajevoEurope/Simferopol…………etc

Also Read,

• PL/SQL EXTRACT function for oracle datetime

• ALTER TABLE to ADD PRIMARY KEY in Oracle| Oracle ALTER TABLE
• oracle date difference | date difference in sql an…
• DATE Data type in Oracle|Oracle Dates and Times|Dates in Oracle
• Formatting date in SQL (Oracle) -Simple date forma…

• Database Schema | Oracle Schema Objects | What is database schema?

• Database Schema | Oracle Schema Objects | What is database schema?
• Autonomous Transactions in PL/SQL
• SQL – Transaction Statements -Transaction Manageme…

• Savepoints In SQL Transactions. – SAVEPOINT
• Oracle Tablespace, and Datafiles – Introduction
• Formatting date in SQL (Oracle) -Simple date forma…
• FINDING database objects, finding valid and INVALI…

Technorati Tags:
ORA-01882, TIMEZONE REGION NOT FOUND, ORA-01882: TIMEZONE REGION NOT FOUND, TIMEZONE REGION