Will Way

Hi,

Thanx for a quick reply. But are you sure,
Because, Hibernet 3.0 Docs says,

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.

No where in the docs they have mentioned that hibernet 3.0 doesn't support stored procedures with more than one out parameters.

What do you think??.......

I think your procedure is not returning all the parameters which you have specified in the mapping specially the ID. Recheck your mapping and try again.

I was getting a similar error but got through it after resolving the mapping problem.

I am posting my procedure definition:-

create or replace PROCEDURE GetTestProc (p_test OUT SYS_REFCURSOR, p_test1 OUT SYS_REFCURSOR) as
BEGIN
OPEN p_test FOR
SELECT name as n1
FROM TEST ;
OPEN p_test1 FOR
SELECT name as n2
FROM TEST
WHERE PROJECT = 'ABC';
END;

Hi,

Can anyone tell me how can i execute oracle stored procedure in Hibernet which has more than one out parameter.

I have been able to execute stored procedure which has one out parameter but the same method is not working with procedures containing more than one out parameter.

Please help.

I have specified the mapping in hbm file. But, Its giving a very annoying exception. Please Help.

Also, Please let me know about any of the link containing docs or examples for procedure calling with Hibernet.

My Code Details:-

<sql-query name="testCallableQuery" callable="true">
<return alias="proc" class="vo.GtJobs"/>
{ call erp.GetJob(?) }
</sql-query>

List dataList = ss.getNamedQuery("testCallableQuery").list();
Iterator itr = dataList.iterator();

ERROR:

19:25:47,578 INFO [SessionFactoryImpl] Checking 0 named queries
19:25:47,687 INFO [STDOUT] Hibernate: { call erp.GetJob(?) }
19:25:47,859 WARN [JDBCExceptionReporter] SQL Error: 17006, SQLState: null
19:25:47,859 ERROR [JDBCExceptionReporter] Invalid column name
19:25:47,859 INFO [STDOUT] I am in JSP
19:25:47,875 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
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 org.apache.jsp.index_jsp._jspService(org.apache.jsp.index_jsp:95)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:159)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:59)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:5971)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1527)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1482)
at org.hibernate.type.StringType.get(StringType.java:16)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:759)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
at org.hibernate.loader.Loader.doQuery(Loader.java:412)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 30 more





[ May 31, 2006: Message edited by: Will Way ]