Dear All,
Iam trying to connect to establish a connection to an oracle database using JDBC thin client. Iam getting the following error.
Could anyone please help me with a solution
Operating System: Oracle linux
Database Version : 12c
java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:144)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:73)
at com.sunopsis.sql.SnpsConnection.testConnection(SnpsConnection.java:1243)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.getLocalConnect(SnpsDialogTestConnet.java:173)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.access$400(SnpsDialogTestConnet.java:51)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$5.doInBackground(SnpsDialogTestConnet.java:629)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$5.doInBackground(SnpsDialogTestConnet.java:625)
at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)
at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:947)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:144)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:73)
at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter.getConnection(OnConnectOnDisconnectDataSourceAdapter.java:87)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter$ConnectionProcessor.run(LoginTimeoutDatasourceAdapter.java:228)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
… 1 more
Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:466)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:391)
at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1126)
at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:503)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:546)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:436)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:1021)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:682)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:789)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:608)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:412)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:385)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:352)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:331)
… 6 more
Best Regards
ORA-01882: timezone region not found
Scenario:
Access an Oracle Database Table from Java Application.
Error:
java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found.
Reason for the error:
The error occurs due to invalid time zone settings used in editors such as eclipse or netbeans or when using an invalid JDBC driver.
Fix 1:
- Check the versions for Oracle JDBC driver and Oracle database.
- Ojdbc6.jar version 11.1.0.7.0 works well with Oracle 9.2.0.4.0 server than ojdbc6.jar (version 11.2.0.3.0).
Fix 2:
Apply the following settings in Netbeans:
- Right-click on the project ->select properties -> choose Run under categories:
under VM Options enter:
- Duser.timezone=UTC or -Duser.timezone=GMT.
- Click Ok, then re-run your program.
Fix 3:
Apply the following settings in eclipse:
- Go to run -> configuration. Under JRE tab in VM Arguments section use the below settings:
Posted by
on February 29, 2012
Problem:
While trying to connect to an Oracle 9.2.0.3 database on EBS 11.5.9 (running on Red Hat Enterprise Linux AS release 4) using SQL Developer 3.0.04, the following message appears:
An error was encountered performing the requested operations: ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found OO604. 00000 - "error occurred at recursive SQL level %s" *Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables). *Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support Vendor code 604
Solution:
Change the timezone variable in sqldeveloper.conf to a valid one for the database.
In SQL Developer, click the Help menu, and then click About. In the About SQL Developer dialog box, click the Properties tab. Scroll down to the end of the list. user.country is set to ‘US’ and user.language is set to ‘en’, so neither of these two values were changed. I then checked user.timezone property. On my system, it was America/La_Paz.
Next, connect to the database (perhaps by logging on to the server via PuTTY or VNC) and query the V$TIMEZONE_NAMES view for valid values:
SELECT * FROM V$TIMEZONE_NAMES WHERE TZNAME LIKE 'America%';
From the rows returned, select a valid timezone name that is in the same timezone shown in SQL Developer properties. In my example, I used ‘America/Santiago’.
Edit the sqldeveloper.conf file (sqldevelopersqldeveloperbinsqldeveloper.conf) and add the following line:
AddVMOption -Duser.timezone=America/Santiago
Save sqldeveloper.conf, exit SQL Developer and then reload.
Reference:
https://forums.oracle.com/forums/thread.jspa?threadID=2256101
https://forums.oracle.com/forums/thread.jspa?messageID=9630177
https://forums.oracle.com/forums/thread.jspa?threadID=2196497
[amazon asin=1847196268&template=iframe image&chan=default] [amazon asin=0071484744&template=iframe image&chan=default] [amazon asin=0137142838&template=iframe image&chan=default]
While trying to create a connection pool to oracle db , following error occurs in weblogic app server.
ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440) oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389) oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382) oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:573) oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:431) oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445) oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191) oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:366) oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:752) oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:366) oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:538) oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228) oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32) oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521) oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280) oracle.jdbc.xa.client.OracleXADataSource.getPooledConnection(OracleXADataSource.java:482) oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:156) oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:101) com.bea.console.utils.jdbc.JDBCUtils.testConnection(JDBCUtils.java:745) com.bea.console.actions.jdbc.datasources.createjdbcdatasource.CreateJDBCDataSource.testConnectionConfiguration(CreateJDBCDataSource.java:458)
The above time zone error usually comes as weblogic queries DB for timezone status.
The above error will show up when there is timezone difference between the database machine and the weblogic application server JVM.
To solve this error please follow below steps:
It seems that each server(DB and weblogic) has a different timezone at OS level.
To resolve the timezone issue we need that both DB and in the JVM should have same timezone set.
We have two ways of doing it:
1. Set same timezone in both the DB and weblogic machine at OS label.(Ask your administrator to do so)
2. If OS label is not allowed you can go for the below JVM label, i.e by setting the –Duser.timezone=<your_DB_time_zone> startup parameter in Java option used to start weblogic server.
-Duser.timezone=GMT
Note : You have to set the JVM timezone to the same timezone as the DB has! After setting the timezone, a complete restart is required to make the changes take effect.
In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.
Problem
When trying to collect events from an Oracle database, it resulted in the error ORA-1882
Symptom
Unable to receive events from Oracle database.
Diagnosing The Problem
Review /var/log/qradar.log
Look for an entry similar to:
ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
Run the below command on the Oracle database command line to review the supported time zones in the database:
select distinct tzname from v$timezone_names
Resolving The Problem
Change the time zone of the QRadar server to a time zone supported by the Oracle database or add the missing time zone to the Oracle database.
For additional assistance. See Developer Works Protocol-JDBC-7.2
Where do you find more information?
[{«Product»:{«code»:»SSBQAC»,»label»:»IBM Security QRadar SIEM»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Integrations — 3rd Party»,»Platform»:[{«code»:»PF016″,»label»:»Linux»}],»Version»:»7.2″,»Edition»:»All Editions»,»Line of Business»:{«code»:»LOB24″,»label»:»Security Software»}}]
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
20 Answers
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).
In a plain a SQL-Developer installation under Windows go to directory
C:Program Filessqldevelopersqldeveloperbin
and add
AddVMOption -Duser.timezone=CET
to file sqldeveloper.conf.
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!!
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
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.
-
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
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.
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.
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"
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.
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.
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.
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>Middlewarejdeveloperjdevbinjdev.conf».
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 ?";
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/clocke.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.
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.
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);
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
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.
Happens when you use the wrong version of OJDBC jar.
You need to use
11.2.0.4
The server startup failed and the status changed to «FAILED_NOT_RESTARTABLE» with the following error.
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services — 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
Error Code: 604
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:326)
at org.eclipse.persistence.sessions.DefaultConnector.connect(DefaultConnector.java:138)
at org.eclipse.persistence.sessions.DatasourceLogin.connectToDatasource(DatasourceLogin.java:162)
at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.setOrDetectDatasource(DatabaseSessionImpl.java:204)
at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.loginAndDetectDatasource(DatabaseSessionImpl.java:741)
at org.eclipse.persistence.internal.jpa.EntityManagerFactoryProvider.login(EntityManagerFactoryProvider.java:239)
at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:685)
… 93 more
Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:466)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:391)
at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1126)
at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:507)
The root cause of the issue is, the server not able to get the time zone configuration
To fix the issue add «-Duser.timezone=UTC» to the server startup parameters — change the timezone with the value supported by database server,
