1 Reply Latest reply on Jan 17, 2011 3:19 AM by Wolf-Dieter Fink

    Oracle timezone problems & JBoss

    Martin Novák Newbie

      Hello, we have a database that contains a table with a column of type TIMESTAMP WITH LOCAL TIME ZONE. We have two applications, one runs on Glassfish and works well. But the other one runs on JBoss 4.2.3, and we are getting the following error:

       

      Caused by: java.sql.SQLException: The database session time zone is not set

              at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)

              at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)

              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)

              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)

              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)

              at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)

              at oracle.jdbc.driver.TimestampltzAccessor.getTimestamp(TimestampltzAccessor.java:298)

              at oracle.jdbc.driver.OracleResultSetImpl.getTimestamp(OracleResultSetImpl.java:1060)

              at oracle.jdbc.driver.OracleResultSet.getTimestamp(OracleResultSet.java:512)

              at org.jboss.resource.adapter.jdbc.WrappedResultSet.getTimestamp(WrappedResultSet.java:979)

              at org.hibernate.type.TimestampType.get(TimestampType.java:30)

              at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)

              at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)

              at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)

              at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2096)

              at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1380)

              at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1308)

              at org.hibernate.loader.Loader.getRow(Loader.java:1206)

              at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:580)

              at org.hibernate.loader.Loader.doQuery(Loader.java:701)

              at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)

              at org.hibernate.loader.Loader.doList(Loader.java:2220)

       

      This error occurs just when we read from that field, not when we write to it. However the strange thing is that the reading works just fine on Glassfish and fails on JBoss. I have noticed that Glassfish uses directly OracleDataSource whereas JBoss does not. When I tried to use OracleDataSource on JBoss, the error was still the same. I also tried both OCI and THIN and there was no difference. It seems to me that this has something to do with Oracle JDBC formatting.

       

      I have found on the internet some solutions that basically say that we should set the timezone on the oracle connection (OracleConnection) manually. This is what we don't want to do, actually. And I believe if reading from local timezone column works on Glassfish, it should work on JBoss as well. But I have no idea how to set it up. Has anyone came across to this problem?

      thanks,

      Martin