File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Object Relational Mapping and the fly likes Hibernate/Native SQL and null inparams Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate/Native SQL and null inparams" Watch "Hibernate/Native SQL and null inparams" New topic

Hibernate/Native SQL and null inparams

Mathias Berg

Joined: Oct 03, 2007
Posts: 5
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, -> 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"

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

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

{ ? = call selectAllCases(:id, ate_from) }

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(
at org.hibernate.exception.JDBCExceptionHelper.convert(
at org.hibernate.loader.Loader.doList(
at org.hibernate.loader.Loader.listIgnoreQueryCache(
at org.hibernate.loader.Loader.list(
at org.hibernate.loader.custom.CustomLoader.list(
at org.hibernate.impl.SessionImpl.listCustomQuery(
at org.hibernate.impl.AbstractSessionImpl.list(
at org.hibernate.impl.SQLQueryImpl.list(
at org.springframework.orm.hibernate3.HibernateTemplate$33.doInHibernate(
at org.springframework.orm.hibernate3.HibernateTemplate.execute(
at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQueryAndNamedParam(
at CaseDaoHibernate.getTestSP(
at CaseDaoTest.testCaseSearch(
Mathias Berg

Joined: Oct 03, 2007
Posts: 5
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?
I agree. Here's the link:
subject: Hibernate/Native SQL and null inparams
It's not a secret anymore!