Hibernate/Native SQL and null inparams

[Mathias Berg]

Hi i wonder if i could get some help regarding a small but so irretation thing.

Is there a problem of using Timestamp as null in a call to a stored procedure, by using Hibernate and Native SQL to to the call.
(im using this as an example, http://www.hibernate.org/hib_docs/v3/reference/en/pdf/hibernate_reference.pdf -> 16.2.2. Using stored procedures for querying)

Im using Spring 2.3, Hibernate 3, Oracle 10g database.

My DAO look like this.
-----------------------------------------

...
List<TestSP> testSP = getHibernateTemplate().findByNamedQueryAndNamedParam
("selectAllCases_sp", new String[]{"id", "date_from"}
, new Object[]{"73",null}
);

....

My Hibernate stored procedure mapping file.
-----------------------------------------

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>

<class name="TestSP" mutable="false">
<id name="Id" column="id" type="string" >
<generator class="assigned"/>
</id>
<property name="type" column="type"/>
</class>

<sql-query name="selectAllCases_sp" callable="true">
<return alias="testSP" class="TestSP">
<return-property name="Id" column="id"/>
<return-property name="type" column="type"/>

</return>
{ ? = call selectAllCases(:id, ate_from) }
</sql-query>
</hibernate-mapping>


My Exception is this
-------------------------------------------------------
....
2007-11-13 16:55:50,522 DEBUG org.hibernate.jdbc.AbstractBatcher(line:393) - { ? = call selectAllCases(?, ?) }
2007-11-13 16:55:50,569 WARN org.hibernate.util.JDBCExceptionReporter(line:77) - SQL Error: 6550, SQLState: 65000
2007-11-13 16:55:50,569 ERROR org.hibernate.util.JDBCExceptionReporter(line:78) - ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'SELECTALLCASES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

2007-11-13 16:55:50,569 ERROR CaseDaoHibernate(line:86) - error
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
Caused by:
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2214)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at org.springframework.orm.hibernate3.HibernateTemplate$33.doInHibernate(HibernateTemplate.java:940)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:369)
at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQueryAndNamedParam(HibernateTemplate.java:931)
at CaseDaoHibernate.getTestSP(CaseDaoHibernate.java:80)
at CaseDaoTest.testCaseSearch(CaseDaoTest.java:85)
.....

[Mathias Berg]

Response to myself

My inparam to the SP wich is a timestamp. If its null then i get this error

PLS-00306: wrong number or types of arguments in call to 'SELECTALLCASES'

But if a replace the null with a emtpy string "" then it doesnt throw a exception.
So it seems my Hibernate query skips the inpara if its null (This only happens for Timestamps though, for varchar2 it works fine with null)

Any clues, would be apreciated?