These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 
  Previous topic | Next topic 
Author Message
ashish_gandhi76
 Post subject: call oracle stored procedure from hibernate
PostPosted: Wed Apr 19, 2006 3:59 am 
Newbie

Joined: Wed Apr 12, 2006 2:43 pm
Posts: 2
Location: london
I am new to the use of hibernate to call stored procedure. I am using hibernate 3.0.1 and oracle 9i

My stored procedure is


Code:
CREATE OR REPLACE PACKAGE BODY Locator_Pkg
AS
   PROCEDURE GetCandidatePlaceNames(
        o_rs         OUT Locator_Pkg.rsCursor,
        i_PlaceName   IN VARCHAR2
        )
   AS
     l_errEmplid VARCHAR2(4000);
   BEGIN
           OPEN o_rs FOR
            SELECT ROWNUM p_id,pn.pn_name name, cn.cn_name COUNTY,
                   pn.pn_northing northing, pn.pn_easting easting
            FROM   PLACE_NAME pn, COUNTY cn
            WHERE  pn.cn_id = cn.cn_id
            AND    pn.pn_name_upper LIKE UPPER(i_PlaceName) || '%';

   END GetCandidatePlaceNames;      
END Locator_Pkg;
/





My exception is

2006-04-19 08:47:53,678 DEBUG [org.hibernate.jdbc.AbstractBatcher:298] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-04-19 08:47:53,693 DEBUG [org.hibernate.jdbc.AbstractBatcher:416] closing statement
2006-04-19 08:47:53,709 DEBUG [org.hibernate.util.JDBCExceptionReporter:63] could not execute query [{ call Locator_Pkg.GetCandidatePlaceNames( ? ) }]
java.sql.SQLException: ORA-01006: bind variable does not exist

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1094)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2132)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2015)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2877)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:684)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:276)
at org.hibernate.dialect.Oracle9Dialect.getResultSet(Oracle9Dialect.java:309)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:127)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1269)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
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.bupa.infra.locator.appservice.dao.HibernateLocatorDAO.getCandidatePlaceNames(HibernateLocatorDAO.java:139)
at com.bupa.infra.locator.appservice.util.LocatorManagerImpl.getCandidatePlaceNames(LocatorManagerImpl.java:56)
at com.bupa.infra.locator.appservice.ejb.LocatorServiceBean.getCandidatePlaceNames(LocatorServiceBean.java:117)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:683)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185)
at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:72)
at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84)
at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:315)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:148)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:120)
at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
at org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionContainer.java:331)
at org.jboss.ejb.Container.invoke(Container.java:723)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:60)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:61)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:53)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:185)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:473)
at org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:97)
at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:90)
at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:46)
at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:53)
at org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke(StatelessSessionInterceptor.java:100)
at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:85)
at $Proxy117.getCandidatePlaceNames(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:402)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:309)
at org.jboss.net.axis.server.EJBProvider.processMessage(EJBProvider.java:260)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:333)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:71)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:150)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:120)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:481)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:323)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:880)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:339)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:72)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:275)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)
2006-04-19 08:47:53,771 WARN [org.hibernate.util.JDBCExceptionReporter:71] SQL Error: 1006, SQLState: 72000
2006-04-19 08:47:53,787 ERROR [org.hibernate.util.JDBCExceptionReporter:72] ORA-01006: bind variable does not exist


My call to the stored procedure from dao is

Query q = this.getHibernateSession().getNamedQuery("GetCandidatePlaceNames");
q.setParameter("i_PlaceName", placeName);
List l = q.list();



My hibernate mapping is

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-access="property" default-lazy="false">

<class name="com.bupa.infra.locator.appservice.dao.PlaceNameDO">
<id name="id" type="string"/>
<property name="name" type="string"/>
<property name="county" type="string"/>
<property name="northing" type="string"/>
<property name="easting" type="string"/>
</class>

<sql-query name="GetCandidatePlaceNames" callable="true">
<return alias="candidatePlaceName" class="com.bupa.infra.locator.appservice.dao.PlaceNameDO">
<return-property name="id" column="P_ID"/>
<return-property name="name" column="NAME"/>
<return-property name="county" column="COUNTY"/>
<return-property name="northing" column="NORTHING"/>
<return-property name="easting" column="EASTING"/>
</return>
{ call Locator_Pkg.GetCandidatePlaceNames( :i_PlaceName ) }
</sql-query>
</hibernate-mapping>

Can someone help?
Top
 Profile  
 
pepelnm
 Post subject:
PostPosted: Wed Apr 19, 2006 4:27 am 
Expert
Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster
try
Code:
{ ? = call Locator_Pkg.GetCandidatePlaceNames( :i_PlaceName ) }

instead of
Code:
{ call Locator_Pkg.GetCandidatePlaceNames( :i_PlaceName ) }

in the mapping file
Top
 Profile  
 
ashish_gandhi76
 Post subject:
PostPosted: Wed Apr 19, 2006 6:05 am 
Newbie

Joined: Wed Apr 12, 2006 2:43 pm
Posts: 2
Location: london
Same error with this also....


2006-04-19 10:56:01,928 WARN [org.hibernate.util.JDBCExceptionReporter:71] SQL Error: 1006, SQLState: 72000
2006-04-19 10:56:01,928 ERROR [org.hibernate.util.JDBCExceptionReporter:72] ORA-01006: bind variable does not exist

2006-04-19 11:00:32,146 WARN [org.jboss.tm.TransactionImpl:237] Transaction TransactionImpl:XidImpl [FormatId=257, GlobalId=LBUPAJJSZ71J//21, BranchQual=] timed out. status=STATUS_ACTIVE
2006-04-19 11:02:20,459 ERROR [com.bupa.infra.locator.appservice.dao.HibernateLocatorDAO:145] org.hibernate.exception.GenericJDBCException: could not execute query
2006-04-19 11:02:20,459 ERROR [com.bupa.infra.locator.appservice.ejb.LocatorServiceBean:229] org.hibernate.exception.GenericJDBCException: could not execute query
com.bupa.framework.common.exception.BupaDataAccessException: org.hibernate.exception.GenericJDBCException: could not execute query
at com.bupa.infra.locator.appservice.dao.HibernateLocatorDAO.getCandidatePlaceNames(HibernateLocatorDAO.java:146)
at com.bupa.infra.locator.appservice.util.LocatorManagerImpl.getCandidatePlaceNames(LocatorManagerImpl.java:56)
at com.bupa.infra.locator.appservice.ejb.LocatorServiceBean.getCandidatePlaceNames(LocatorServiceBean.java:117)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
Top
 Profile  
 
pepelnm
 Post subject:
PostPosted: Wed Apr 19, 2006 7:12 am 
Expert
Expert

Joined: Thu Sep 22, 2005 10:29 am
Posts: 285
Location: Almassera/Valencia/Spain/EU/Earth/Solar system/Milky Way/Local Group/Virgo Supercluster
hmm, then try with this
Code:
{ call Locator_Pkg.GetCandidatePlaceNames( ?, :i_PlaceName ) }
Top
 Profile  
 
max
 Post subject:
PostPosted: Wed Apr 19, 2006 8:24 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
read the docs and actually write a stored procedure and function that returns a resultset as specifed in the docs.

_________________
Max
Don't forget to rate

Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.