ORA-01882: timezone region not found

Question

I'm accessing an Oracle Database from a java application, when I run my application I get the following error:

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

I'm running the Java application on the command line. Windows 7 64bit, but Oracle DB is running on a remote Unix server. — ndalama, Feb 06 '12 at 07:23
try to add "-Duser.timezone=" to your command, don't forget to replace with your GMT, ie -Duser.timezone="+05:30" — A.B.Cade, Feb 06 '12 at 07:29

Matteo Steccolini · Accepted Answer · 2020-10-05T09:48:37.347

115

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

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

Then, one can add -Doracle.jdbc.timezoneAsRegion=false to the command line, or AddVMOption -Doracle.jdbc.timezoneAsRegion=false in config files that use this notation.

You can also do this programmatically, e.g. with System.setProperty.

In some cases you can add the environment variable on a per-connection basis if that's allowed (SQL Developer allows this in the "Advanced" connection properties; I verified it to work when connecting to a database that doesn't have the problem and using a database link to a database which has).

edited Oct 05 '20 at 09:48

answered Feb 07 '12 at 13:40

Matteo Steccolini

1,773
1
15
21

24

I also managed to make ojdbc6.jar version 11.2.0.3.0 connect without error, by adding oracle.jdbc.timezoneAsRegion=false in file oracle/jdbc/defaultConnectionProperties.properties (inside the jar). Found this solution here: https://forums.oracle.com/forums/thread.jspa?threadID=1095807 – Matteo Steccolini Mar 15 '12 at 08:11
33

Lastly, one can add -Doracle.jdbc.timezoneAsRegion=false to the command line, or AddVMOption -Doracle.jdbc.timezoneAsRegion=false in config files that use this notation. – Matteo Steccolini Apr 29 '13 at 15:01
Thanks stmsat. It worked for me. I changed my ojdbc jar to 11.1.0.7.0 version in tomcat/lib directory and it started working :). – mdev Dec 30 '14 at 06:39
1

I am using maven to build and package(war) my project and deploy in Cloud Flare, is there any way setting this property can be done from *application.properties* file or from maven. – Ismail Nov 28 '19 at 11:10
Or maybe you can change system timezone, that worked for me (e.g. sudo timedatectl set-timezone you_zone) – kolle_86 Jun 30 '23 at 11:30

score 48 · Answer 2 · answered Nov 12 '13 at 10:59

48

In a plain a SQL-Developer installation under Windows go to directory

C:\Program Files\sqldeveloper\sqldeveloper\bin

and add

AddVMOption -Duser.timezone=CET

to file sqldeveloper.conf.

answered Nov 12 '13 at 10:59

Marcus Rickert

4,138
3
24
29

Using Jetbrains DataGrip and faced the same issue after adding this VM Option the problem was solved – latsha Jul 28 '16 at 04:50
Thanks, this really helped a lot! – Quinton May 24 '18 at 13:32

score 29 · Answer 3 · answered Aug 04 '12 at 16:37

Error I got :

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

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

Prev 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 is working!!

I don't know why it did not like the default timezone 'Europe/Madrid'. Setting the default timezone to 'GMT' works. — fgui, Jun 22 '16 at 09:57

Babatunde Adeyemi · Answer 4 · 2013-11-27T20:31:34.773

24

Update the file oracle/jdbc/defaultConnectionProperties.properties in whatever version of the library (i.e. inside your jar) you are using to contain the line below:

oracle.jdbc.timezoneAsRegion=false

edited Nov 27 '13 at 20:31

answered Nov 22 '13 at 11:08

Babatunde Adeyemi

14,360
4
34
26

score 21 · Answer 5 · answered Mar 31 '14 at 12:22

What happens is, that the JDBC client sends the timezone ID to the Server. The server needs to know that zone. You can check with

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

I have some db servers which know about 'Etc/UTC' and 'UTC' (tzfile version 18) but others only know 'UTC' (tz version 11).

SELECT FILENAME,VERSION from V$TIMEZONE_FILE;

There is also different behavior on the JDBC client side. Starting with 11.2 the driver will sent the zone IDs if it is "known" to Oracle, whereas before it sent the time offset. The problem with this "sending of known IDs" is, that the client does not check what timezone version/content is present on the server but has its own list.

This is explained in Oracle Support Article [ID 1068063.1].

It seems it also depends on the Client OS, it was more likely that Etc/UTC fails with Ubuntu than RHEL or Windows. I guess this is due to some normalization but I haven't figured out what exactly.

score 17 · Answer 6 · answered Nov 13 '12 at 14:39

17

in eclipse go run - > run configuration
in there go to JRE tab in right side panels
in VM Arguments section paste this

-Duser.timezone=GMT
then Apply - > Run

answered Nov 13 '12 at 14:39

Priyan at Dialog

309
1
6
16

score 12 · Answer 7 · answered Oct 28 '14 at 18:45

12

I had this problem when running automated tests from a continuous integration server. I tried adding the VM argument "-Duser.timezone=GMT" to the build parameters, but that didn't solve the problem. However, adding the environment variable "TZ=GMT" did fix it for me.

answered Oct 28 '14 at 18:45

Matt

1,284
14
22

3

The "TZ=GMT" environment variable also worked for me. I was having problems with a shell script that set up the environment for running a separate tool that, in turn, accessed Oracle. – David Keener Oct 14 '15 at 20:07
1

Having the same issue, I too had to use "TZ=Europe/Zurich" when using "ant". It worked! – Christof Kälin Jun 28 '18 at 09:00
2

This should be the top answer. All other answers didn't work. – Alex Dembo Sep 12 '19 at 12:45
in docker this worked for me `TZ=America/Los_Angeles` – JRichardsz May 14 '21 at 22:59
Adding TZ=IST worked for me when jenkins build located in GMT+2 was trying to access database located in India. – Harshita Dec 20 '21 at 11:35

score 9 · Answer 8 · answered Nov 01 '17 at 19:59

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

JAVA_OPTS=-Doracle.jdbc.timezoneAsRegion=false

I'm sure that using one of the Java parameter suggestions from the other answers would work in the same way.

score 4 · Answer 9 · edited Aug 16 '18 at 08:31

4

ERROR :

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

Solution: CIM setup in Centos.

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

Add this java arguments:

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

edited Aug 16 '18 at 08:31

Ahmed Ashour

5,179
10
35
56

answered Aug 09 '16 at 03:53

Guna Sekaran

553
4
5

score 3 · Answer 10 · answered Mar 22 '13 at 18:14

3

In Netbeans,

Right-click your project -> Properties
Go to Run (under Categories)
Enter -Duser.timezone=UTC or -Duser.timezone=GMT under VM Options.

Click Ok, then re-run your program.

Note: You can as well set to other timestones besides UTC & GMT.

answered Mar 22 '13 at 18:14

Babatunde Adeyemi

14,360
4
34
26

score 3 · Answer 11 · edited Aug 16 '18 at 08:32

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

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

select TZNAME from V$TIMEZONE_NAMES;

Along with that you'd want to check the time zone settings in your jdev.conf as well as in the JDeveloper -> Application Menu -> Default Project Propertes -> Run/Debug -> Default Profile -> Run Options.

score 2 · Answer 12 · edited Jun 20 '20 at 09:12

2

I also same faced similar issue.

Environment:

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

Resolution

TZ parameter was not set in linux machine, that basically tell oracle about the timezone. So, After adding export statment "export TZ=UTC" at time of application start solved my problem.

UTC--> Change accorind to your timezone.

edited Jun 20 '20 at 09:12

Community

1
1

answered May 13 '18 at 09:58

user3721248

21
1

score 2 · Answer 13 · answered Oct 27 '18 at 21:58

I had the same problem when trying to make a connection on OBIEE to Oracle db. I changed my Windows timezone from (GMT+01:00) West Central Africa to (GMT+01:00) Brussels, Copenhagen, Madrid, Paris. Then I rebooted my computer and it worked just fine. Seems like Oracle was not able to recognize the west central Africa timezone.

score 2 · Answer 14 · answered Apr 23 '20 at 08:43

This issue happens as the code which is trying to connect to db, has a timezone which is not in db. It can also be resolved by setting the time zone as below or any valid time zone available in oracle db. valid time zone which can be found select * from v$version;

System.setProperty("user.timezone", "America/New_York"); TimeZone.setDefault(null);

score 1 · Answer 15 · edited Oct 17 '14 at 11:35

I too had the same problem when i tried to create connection in JDeveloper. Our server located in different timezone and hence it raised the below errors as:

ORA-00604: error occurred at recursive SQL level 1 

ORA-01882: timezone region not found

I referred many forums which asked to include timezone in the Java Options(Run/Debug/Profile) of Project properties and Default Project properties as -Duser.timezone="+02:00" bBut it didn't work for me. Finally the following solution worked for me.

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

AddVMOption -Duser.timezone=UTC+02:00

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

score 1 · Answer 16 · edited Aug 16 '18 at 08:32

1

I was able to solve the same issue by setting the timezone in my linux system (Centos6.5).

Reposting from

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

set timezone in /etc/sysconfig/clock e.g. set to ZONE="America/Los_Angeles"
sudo ln -sf /usr/share/zoneinfo/America/Phoenix /etc/localtime

To figure out the timezone value try to

ls /usr/share/zoneinfo

and look for the file that represents your timezone.

Once you've set these reboot the machine and try again.

edited Aug 16 '18 at 08:32

Ahmed Ashour

5,179
10
35
56

answered Apr 23 '15 at 23:37

adi-pradhan

353
2
10

2

For me, making sure that /etc/sysconfig/clock and the /etc/localtime link were set properly was basically a prerequisite for solving the ORA-01882 issue. – David Keener Oct 14 '15 at 20:10

score 1 · Answer 17 · answered Jun 02 '16 at 10:55

1

In my case I could get the query working by changing "TZR" with "TZD"..

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

answered Jun 02 '16 at 10:55

Laura Liparulo

2,849
26
27

score 0 · Answer 18 · answered Jul 28 '16 at 07:47

0

Facing the same issue using Eclipse and a distant Oracle Database, changing my system time zone to match the time zone of the database server fixed the problem. Re-start the machine after changing system time zone.

I hope this can help someone

answered Jul 28 '16 at 07:47

S_intg

182
1
10

score 0 · Answer 19 · edited Nov 08 '17 at 09:18

0

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

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

e.g. Indian time zone is chennai,kolkata.

edited Nov 08 '17 at 09:18

cf_en

1,661
1
10
18

answered Nov 08 '17 at 08:59

manjunatha H

1

score 0 · Answer 20 · answered Dec 29 '20 at 18:15

0

Happens when you use the wrong version of OJDBC jar.

You need to use 11.2.0.4

answered Dec 29 '20 at 18:15

simkeyur

36
1
5

score 0 · Answer 21 · answered Apr 28 '22 at 12:15

0

For my case, i set the timezone at my OS level (ubuntu) with this command.

timedatectl set-timezone {timezone}

For example,

timedatectl set-timezone Africa/Kampala

answered Apr 28 '22 at 12:15

Lutaaya Fauzi

21
3

Ishimwe Aubain Consolateur · Answer 22 · 2022-08-30T11:22:18.087

This might be a bit late but It may help someone. I encountered this issue while working on spring-boot application and failed to connect to the Oracle DB.

Error:

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1

ORA-01882: timezone region not found

SELECT DBTIMEZONE FROM dual; -- This return: +00:00
timedatectl #The OS on the other hand returned correct time and timezone

Since I am not a system admin and not allowed to change system config, I had to apply a workaround in the codes.

spring.datasource.hikari.data-source-properties.oracle.jdbc.timezoneAsRegion=false
#Added the above into the application property file

The working solution came from the below link

Working solution URL

Edit

After the DBA patched the oracle with the missing timezone patches, The above (...timezoneAsRegion=false) was no longer needed.

score 0 · Answer 23 · answered Sep 20 '22 at 16:29

For Spring-Boot Application - // add below two lines

@SpringBootApplication
public class  Application {
  public static void main(String[] args) {
     // add below two lines
      System.out.println("Setting the timezone"+TimeZone.getTimeZone("GMT+9:00").getID());
      TimeZone.setDefault(TimeZone.getTimeZone("GMT+9:00"));
      SpringApplication.run(Application.class, args);
  }
}

ORA-01882: timezone region not found

23 Answers23

Environment:

Resolution

Linked

Related