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?
Have you checked out Aspose?
subject: Hibernate/Native SQL and null inparams
It's not a secret anymore!