Native SQL on ORACLE 9 with date type

Hanan · **Posted:** Thu Nov 10, 2005 2:16 pm

In the below code I try to retrieve 3 columns from a table:
START_TIME --> SQL Date (NOT Timestamp)
OUT_PASSIVE_MOS_AVG --> SQL Number(11,2)
OUT_PASSIVE_MOS_MIN --> SQL Number(11,2)

There is always an exception when getting the value of START_TIME (I tried to use different type of values in the addScalar method for that field: TIME, CALENDAR, CALENDAR_DATE) and all of them throw an exception.

When I changed the type of the column (by using SQL functions: TO_TIMESTAMP(TO_CHAR(START_TIME, foramt), foramt)) and used Hibernate.CALENDAR it worked fine, but this is a workaround I do not whish to do.
Changing the type of the column is not an option.

What am I doing wrong?

Thanks in advance,
Hanan.

Hibernate version:
3.0.5

Mapping documents:
N/A

Code between sessionFactory.openSession() and session.close():
List tList = tSession.createSQLQuery(" select" +
" START_TIME," +
" OUT_PASSIVE_MOS_AVG," +
" OUT_PASSIVE_MOS_MIN" +
" from CALL_QR")
.addScalar("START_TIME", Hibernate.DATE)
.addScalar("OUT_PASSIVE_MOS_AVG", Hibernate.DOUBLE)
.addScalar("OUT_PASSIVE_MOS_MIN", Hibernate.DOUBLE)
.list();

Full stack trace of any exception that occurs:
java.lang.NullPointerException
at org.jboss.mx.loading.LoadMgr3.beginLoadTask(LoadMgr3.java:153)
at org.jboss.mx.loading.RepositoryClassLoader.loadClassImpl(RepositoryClassLoader.java:464)
at org.jboss.mx.loading.RepositoryClassLoader.loadClass(RepositoryClassLoader.java:374)
at java.lang.ClassLoader.loadClass(ClassLoader.java:235)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:302)
at oracle.jdbc.dbaccess.DBConversion.DateBytesToDate(DBConversion.java:3045)
at oracle.jdbc.driver.OracleStatement.getDateValue(OracleStatement.java:4790)
at oracle.jdbc.driver.OracleResultSetImpl.getDate(OracleResultSetImpl.java:632)
at oracle.jdbc.driver.OracleResultSet.getDate(OracleResultSet.java:1601)
at org.jboss.resource.adapter.jdbc.WrappedResultSet.getDate(WrappedResultSet.java:283)
at org.hibernate.type.DateType.get(DateType.java:25)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:94)
at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:133)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:329)
at org.hibernate.loader.Loader.doQuery(Loader.java:412)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
at com.radcom.hibernate.qalarms.dao.CalculationDAO.getThresholdValues(CalculationDAO.java:33)

Name and version of the database you are using:
Oracle 9 with Oracle 9 Dialect

The generated SQL (show_sql=true):
19:38:03,750 INFO [STDOUT] Hibernate: select START_TIME, OUT_PASSIVE_MOS_AVG, OUT_PASSIVE_MOS_MIN from CALL_QR

Debug level Hibernate log excerpt:
N/A

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

try with Date

Hanan · **Posted:** Fri Nov 11, 2005 3:27 am

Sorry, I forgot to mention it on my original post, but I did try it with DATE as well, and it also throw an exception.

Hanan.

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

i map oracle date to java date without problem
java.util.Date in POJO, type="date" in xbm.xml and date column in database

Hanan · **Posted:** Fri Nov 11, 2005 4:50 pm

Thanks for the help, but the SQL that I am using is on a non-mapped table (it will be a problem to map it since it is maintain in a different application and I only have API to retrieve fields names and types for custom queries).

Any suggestion?

Thanks again,
Hanan.

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

i try query like your and it work fine
test case (DATD is type date in database)

Code:

public void testNativeSql () {
      Session session = getSessionFactory().openSession();
       List tList = session.createSQLQuery(" select" +
             " DATD," +
             " DUG," +
             " POT" +
             " from FIN_NKS")
             .addScalar("DATD", Hibernate.DATE)
             .addScalar("DUG", Hibernate.DOUBLE)
             .addScalar("POT", Hibernate.DOUBLE)
             .list(); 
       session.close();
   }

do you open session correct ?

Hanan · **Posted:** Sat Nov 12, 2005 5:46 am

Yes I do open a session, if I omit the DATE column it works fine.
What is the version of Hibernate and Oracle driver that you are using?
Do you use Oracle 9 Dialect?

Thanks in advance,
Hanan.

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

Hanan,
yes, it is oracle 9 dialect, driver is 10g v2, database 9.2

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

hibernate 3.1 (cvs before 2-3 weeks, i think that is hibernate 3.1 rc1)

Hanan · **Posted:** Sun Nov 13, 2005 4:36 am

OK, the problem was Oracle driver (I use 9.0.2.0.0), once I downloaded version 10.2.0.1.0 it worked fine.

Thanks for all your help.
Hanan.