unable to execute stored procedure

sudha_parimala · **Joined:** Tue Apr 18, 2006 3:50 am **Posts:** 6

Hibernate version: Hibernate 3.1.2

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


<hibernate-mapping>
<class name="com.medicom.eIP.mapping.PatDtls"
discriminator-value="N">
<id name="id">
<generator class="native" />
</id>
<property name="patLine" column="P_display_key" />
</class>

<sql-query name="callPatLine" callable="true">

<return alias="patDtls"
class="com.medicom.eIP.mapping.PatDtls">
<return-property name="patLine" column="P_display_key" />
</return>
{?=call getpatlinehib('SD00000225')}
</sql-query>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Query query = getHibernateHandle().getSessionFactory().openSession()
.getNamedQuery("callPatLine");

//List list = query.list();

Full stack trace of any exception that occurs:

Hibernate: select * from ( {?=call getpatlinehib('SD00000225')} ) where rownum <= ?
2006-04-18 14:36:09,625 ERROR JDBCExceptionReporter - ORA-00903: invalid table name

org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [{?=call getpatlinehib('SD00000225')}]; nested exception is java.sql.SQLException: ORA-00903: invalid table name

java.sql.SQLException: ORA-00903: invalid table name

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
at oracle.jdbc.driver.T4CCallableStatement.executeForDescribe(T4CCallableStatement.java:798)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CCallableStatement.executeMaybeDescribe(T4CCallableStatement.java:838)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.execute(NewProxyCallableStatement.java:2965)
at org.hibernate.dialect.Oracle9Dialect.getResultSet(Oracle9Dialect.java:278)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:146)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1666)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1674)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:147)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at com.medicom.service.dao.hibernate.AbstractDAO$2.doInHibernate(AbstractDAO.java:294)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:366)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:334)
at com.medicom.service.dao.hibernate.AbstractDAO.getNamedQuery(AbstractDAO.java:279)
at com.medicom.service.dao.hibernate.AbstractDAO.findByCriteria(AbstractDAO.java:73)
at com.medicom.service.dao.hibernate.AbstractDAO.delegateTransaction(AbstractDAO.java:317)
at com.medicom.framework.service.controller.POJOController.process(POJOController.java:30)
at com.medicom.framework.service.ServiceRouter.route(ServiceRouter.java:32)
at com.medicom.framework.service.ServiceRequest.process(ServiceRequest.java:58)
at com.medicom.test.TestIP.main(TestIP.java:40)

Name and version of the database you are using:
Oracle9i

The generated SQL (show_sql=true):

select * from ( {?=call getpatlinehib('SD00000225')} ) where rownum <= ?

Debug level Hibernate log excerpt:
Hibernate: select * from ( {?=call getpatlinehib('SD00000225')} ) where rownum <= ?
2006-04-18 14:38:44,734 DEBUG AbstractBatcher - preparing statement
2006-04-18 14:38:47,968 DEBUG AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-04-18 14:38:47,968 DEBUG AbstractBatcher - closing statement
2006-04-18 14:38:48,000 DEBUG JDBCExceptionReporter - could not execute query [{?=call getpatlinehib('SD00000225')}]
java.sql.SQLException: ORA-00903: invalid table name

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
at oracle.jdbc.driver.T4CCallableStatement.executeForDescribe(T4CCallableStatement.java:798)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CCallableStatement.executeMaybeDescribe(T4CCallableStatement.java:838)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.execute(NewProxyCallableStatement.java:2965)
at org.hibernate.dialect.Oracle9Dialect.getResultSet(Oracle9Dialect.java:278)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:146)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1666)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)

Procedure

CREATE OR REPLACE
FUNCTION medicom.getpatlinehib (P_patient_id IN VARCHAR2)
RETURN SYS_REFCURSOR AS
st_cursor SYS_REFCURSOR;
-- This program unit used to generate the Display Key based on the inputs
-- Patient name, Sex, Age and Patient id.
X_sex VARCHAR2(1);
X_dob VARCHAR2(10);
X_pat_name VARCHAR2(80);
X_pat_name_loc_lang VARCHAR2(80);
X_age_in_days NUMBER;
X_age_unit VARCHAR2(30);
X_year NUMBER(3);
X_month NUMBER(2);
X_days NUMBER(2);
X_Active Varchar2(1);
X_Suspend Varchar2(1);
X_Deceased Varchar2(1);
X_Loc_Lang Varchar2(1);
P_display_key VARCHAR2(1000);

BEGIN
Select Sex, to_char(Date_of_birth,'DD/MM/YYYY') Date_of_birth, Patient_name, Patient_name_loc_lang,
decode(Deceased_yn,'Y', floor(Deceased_date-Date_of_birth),'N',floor(Sysdate-Date_of_birth))
Age_in_days, Active_Yn, Suspend_Yn, Deceased_Yn
into X_sex, X_dob, X_pat_name, X_pat_name_loc_lang, X_age_in_days, X_Active, X_Suspend, X_Deceased
from Mp_patient where Patient_id = P_patient_id;
SELECT CALCULATE_AGE(X_dob, 1) INTO X_year FROM DUAL;
SELECT CALCULATE_AGE(X_dob, 2) INTO X_month FROM DUAL;
SELECT CALCULATE_AGE(X_dob, 3) INTO X_days FROM DUAL;
IF (X_year = 0 and X_month=0) THEN
X_age_unit := to_char(X_days) || 'D ';
ELSIF (X_year = 0) THEN
X_age_unit := to_char(X_month) || 'M ' || to_char(X_days) || 'D ';
ELSE
X_age_unit:= to_char(X_Year) || 'Y '|| to_char(X_month)||'M '|| to_char(X_days) || 'D ';
END IF;
if X_Deceased = 'Y' then
X_Deceased := 'D';
else
X_Deceased := 'N';
end if;
if X_Suspend = 'Y' then
X_Suspend := 'S';
else
X_Suspend := 'N';
end if;
if X_Active = 'N' then
X_Active := 'I';
else
X_Active := 'Y';
end if;
Select Names_in_oth_lang_yn
into X_Loc_Lang
from Mp_Param;
If X_Loc_Lang = 'N' then
P_display_key := X_pat_name || ', ' || X_sex || ', ' || X_age_unit || ', '
|| P_patient_id || ' #' || X_Active || '#' || X_Suspend || '#' || X_Deceased;
Else
if X_pat_name_loc_lang is not null then
X_pat_name_loc_lang := X_pat_name_loc_lang || ', ';
elsif X_pat_name_loc_lang = 'null' then
X_pat_name_loc_lang := '';
else
X_pat_name_loc_lang := '';
end if;
P_display_key := X_pat_name_loc_lang || X_pat_name || ', ' || X_sex || ', ' || X_age_unit || ', '
|| P_patient_id || ' #' || X_Active || '#' || X_Suspend || '#' || X_Deceased;
End if;
OPEN st_cursor FOR SELECT P_display_key FROM DUAL;
RETURN st_cursor;
END;

pepelnm · **Posted:** Tue Apr 18, 2006 7:54 am

From hibernate reference 16.3.2. Using stored procedures for querying

Quote:

For Oracle the following rules apply:
• A function must return a result set. The first parameter of a procedure must be an OUT that returns a result
set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF
CURSOR type, see Oracle literature.

max · **Posted:** Tue Apr 18, 2006 8:30 am

...and

Code:

Hibernate: select * from ( {?=call getpatlinehib('SD00000225')} ) where rownum <= ?

would only be executed if you are setting maxlength etc. on the query so your code is not what you are actually executing.

...and stored procedures does not support rownum tricks, so don't use maxlength etc with them.

egore911 · **Joined:** Thu Oct 18, 2007 10:38 am **Posts:** 7

I agree that setting maxresult would cause that situation. But I'm facing the problem right now and I don't have this set. I migrated from plain hibernate to JPA (entity manager) based. Before I switched it worked fine.

Mapping:
@NamedNativeQuery(name = "X", query = "{? = call XYZ(?, ?)}", resultSetMapping = "XM", hints = { @QueryHint(name = "org.hibernate.callable", value = "true") })
@SqlResultSetMapping(name = XM", columns = { @ColumnResult(name = "x") })

DAO:
final Query q = this.em.createNamedQuery("X");
q.setParameter(1, "whatever");
q.setParameter(2, "whatever");
final String x = (String) q.getSingleResult();

And that's exactly it. "this.em" is a injected entity manager that works flawless. The log shows:

[main] org.hibernate.impl.SessionImpl: SQL query: {? = call XYZ(?, ?)}

and then

[main] org.hibernate.SQL: select * from ( {? = call XYZ(?, ?)} ) where rownum <= ?

And it just does not make any sense to me. I tested if switching between org.hibernate.annotations.NamedNativeQuery and javax.persistence.NamedNativeQuery would make any change but it doesn't.

Can anyone tell me what I'm doing wrong?

Edit:

I found a quick workaround: using

Code:

final String x = (String) q.getResultList().get(0);

instead of

Code:

final String x = (String) q.getSingleResult();

works. I'm not sure whether or not this is a bug in hibernate. At least it's irritating that QueryImpl is calling q.list() in getSingleResult() and fiddling with setMaxResults(). I assumed it would just forward the call to uniqueResult().

max · **Posted:** Mon Feb 16, 2009 7:51 am

don't use getSingleResult then. getSingleResult != getUniqueResult.

egore911 · **Joined:** Thu Oct 18, 2007 10:38 am **Posts:** 7

The issue is that getUniqueResult is not part of javax.persistence.Query. What's the appropriate way to call it then? Cast to org.hibernate.Query?

max · **Posted:** Mon Feb 16, 2009 8:18 pm

yes, or just iterate the result manually.

egore911 · **Joined:** Thu Oct 18, 2007 10:38 am **Posts:** 7

I think I'll go for casting and getSingleResult as I don't like the idea of iterating over the result, checking for "no result", "more than on row", etc. Thanks for the help!