ORA-01882: time zone not found

Question

I access the Oracle database from a Java application, when I run my application, I get the following error:

java.sql.SQLException: ORA-00604: an error occurred at the recursive SQL level 1 ORA-01882: timezone region not found

Answer 1

You can also try checking the version of the Oracle jdbc driver and the Oracle database. Only today I had this problem when using ojdbc6.jar (version 11.2.0.3.0) to connect to an Oracle server 9.2.0.4.0. Replacing it with ojdbc6.jar version 11.1.0.7.0 solved the problem.

I also managed to connect ojdbc6.jar version 11.2.0.3.0 without errors by adding oracle.jdbc.timezoneAsRegion=false to the oracle / jdbc / defaultConnectionProperties.properties file (inside the jar). Found this solution here

Finally, you can add -Doracle.jdbc.timezoneAsRegion=false to the command line or AddVMOption -Doracle.jdbc.timezoneAsRegion=false to the configuration files that use this entry

Answer 2

In a simple installation of SQL-Developer under Windows, go to the directory

 C:\Program Files\sqldeveloper\sqldeveloper\bin 

and add

 AddVMOption -Duser.timezone=CET 

to sqldeveloper.conf file.

Answer 3

Mistake:

Error from db_connection.java →> java.sql.SQLException: ORA-00604: error at recursive SQL level 1 ORA-01882: timezone region not found

ORA-00604: error at recursive SQL level 1ORA-01882: timezone region not found

Previous code:

  public Connection getOracle() throws Exception { Connection conn = null; Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:tap", "username", "pw"); return conn; } 

New Code:

  public Connection getOracle() throws Exception { TimeZone timeZone = TimeZone.getTimeZone("Asia/Kolkata"); TimeZone.setDefault(timeZone); Connection conn = null; Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:tap", "username", "pw"); return conn; } 

now it works !!

Answer 4

Update the oracle / jdbc / defaultConnectionProperties.properties file in any version of the library (i.e. inside your jar) that you use to contain the following line:

 oracle.jdbc.timezoneAsRegion=false 

Answer 5

What happens, the JDBC client sends the timezone identifier to the Server. The server must know this zone. You can check with

 SELECT DISTINCT tzname FROM V$TIMEZONE_NAMES where tzname like 'Etc%'; 

I have some db servers that know about "Etc / UTC" and "UTC" (tzfile version 18), but others only know "UTC" (tzf version 11).

 SELECT FILENAME,VERSION from V$TIMEZONE_FILE; 

On the JDBC client side, there is another behavior. Starting with 11.2, the driver sends zone identifiers if it is "known" by Oracle, whereas before that it sent a temporary offset. The problem with this “sending known identifiers” is that the client does not check which version / content of the time zone is present on the server, but has its own list.

This is explained in the Oracle support article [ID 1068063.1].

It seems that this also depends on the client OS, it is more likely that Etc / UTC will work with Ubuntu than RHEL or Windows. I assume that this is due to some normalization, but I did not understand what exactly.

Answer 6

• in eclipse go run -> configure

• go to the JRE tab in the right pane

• in the VM Arguments section, paste this

  -Duser.timezone=GMT

• then Apply -> Run

Answer 7

I had this problem when running automatic tests from a continuous integration server. I tried adding the VM argument " -Duser.timezone=GMT " to the build options, but this did not solve the problem. However, adding the environment variable " TZ=GMT " really fix for me.

Answer 8

MISTAKE:

ORA-00604: an error occurred at the level of recursive SQL 1 ORA-01882: time zone region not found

Solution: Configure CIM on Centos.

 /opt/oracle/product/ATG/ATG11.2/home/bin/dynamoEnv.sh 

Add these Java arguments:

 JAVA_ARGS="${JAVA_ARGS} -Duser.timezone=EDT" 

Answer 9

At netbeans

• Right click on your project -> Properties
• Go to the "Run" section (in the "Categories" section)
• Type -Duser.timezone = UTC or -Duser.timezone = GMT in the VM Settings section.

Click OK, and then run the program.

Note. You can also set other time units besides UTC and GMT.

Answer 10

If this problem is in JDeveloper: Change the project properties for both the model and the view project → run / debug → default profile → edit and add the following startup parameter: -Duser.timezone = Asia / Calcutta

Make sure the above time zone value is selected from your database as follows:

 select TZNAME from V$TIMEZONE_NAMES; 

Along with this, you will want to check the time zone settings in your jdev.conf, as well as in JDeveloper → Application menu → Default project properties → Run / Debug → Default profile → Launch options.

Answer 11

I ran into this problem with Tomcat. Setting the following to $CATALINA_BASE/bin/setenv.sh solved the problem:

 JAVA_OPTS=-Doracle.jdbc.timezoneAsRegion=false 

I am sure that using one of the Java parameter suggestions from the other answers will work the same.

Answer 12

I also had the same problem when I tried to create a connection in JDeveloper. Our server was in different time zones, and therefore it caused the following errors:

 ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found 

I referred to many forums in which it was suggested to include the time zone in the Java parameters (start / debug / profile) of the project properties and the default project properties as -Duser.timezone="+02:00" b, but this did not work for me . Finally, the following solution worked for me.

Add the following line to the JDeveloper configuration file (jdev.conf).

 AddVMOption -Duser.timezone=UTC+02:00 

The file is located in the "<oracle install root> \ Middleware \ jdeveloper \ jdev \ bin \ jdev.conf" folder.

Answer 13

I was able to solve the same problem by setting the time zone on my Linux system (Centos 6.5).

Reposting from

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/set-time.html

1. set the time zone to /etc/sysconfig/clock for example, set ZONE = "America / Los_Angeles"

2. sudo ln -sf / usr / share / zoneinfo / America / Phoenix / etc / localtime

To find out the time zone, try

 ls /usr/share/zoneinfo 

and find the file that represents your time zone.

After setting up, restart your computer and try again.

Answer 14

In my case, I could get the request to work by changing "TZR" to "TZD" ..

 String query = "select * from table1 to_timestamp_tz(origintime,'dd-mm-yyyy hh24:mi:ss TZD') between ? and ?"; 

Answer 15

I also faced the same problem.

Wednesday:

Linux, hibernate project, ojdbc6 driver when querying oracle 11g database.

resolution

The TZ parameter was not installed on the Linux machine, which basically tells the oracle about the time zone. So, after adding the export statistics "export TZ = UTC" during application startup, my problem is resolved.

UTC -> Change according to your time zone.

Answer 16

Building on the same issue, using Eclipse and a remote Oracle database, changing the system time zone to match the time zone of the database server, the problem has been fixed. Restart the machine after changing the system time zone.

Hope this helps someone.

Answer 17

java.sql.SQLException: ORA-00604: error with recursive SQL level 1 ORA-01882: timezone region not found

For this type of error, just change your system time to your country's default format GMT

eg. Indian time zone - chennai, kolkata.

Answer 18

I had the same problem when trying to establish a connection on OBIEE with Oracle db. I changed my Windows time zone from (GMT + 01: 00) West Central Africa to (GMT + 01: 00) Brussels, Copenhagen, Madrid, Paris. Then I rebooted the computer and it worked fine. It seems that Oracle could not recognize the time zone of West and Central Africa.