[b]please see the following . After Doing following things i got the Exception:
I am using oracl9i.
CREATE TABLE PLAYERTABLE
(
ID INTEGER,
NAME VARCHAR2(80),
PLACE VARCHAR2(80),
KEY VARCHAR2(40)
)[b]

[b]Hibernate version:3.0[/b]

[b]Mapping documents:

In player.hbm.xml
<?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>
<class name="player" dynamic-update="true" table="playertable">
<id name="id" column="ID" unsaved-value="0">
<generator class="increment"/>
</id>
<property name="name" type="string" />
<property name="place" type="string" />
<property name="key" type="string" />
</class>
<sql-query name="selectPlayerMaster_SP" callable="true">
<return alias="paymentMaster" class="player">
<return-property name="name" column="NAME" />
</return>
{call SELECTPLAYERMASTER_SP2(?,:key)}
</sql-query>
</hibernate-mapping>

[/b]

[b]Code between sessionFactory.openSession() and session.close():
Session session = factory.openSession();
tx = session.beginTransaction();
String c = "1000" ;
Query query = session.getNamedQuery("selectPlayerMaster_SP")

.setString("key", c);
List paymentMasterlist = query.list();
[/b]

[b]Full stack trace of any exception that occurs:
Stored Procedure Executed Started
Hibernate: {call SELECTPLAYERMASTER_SP2(?,?)}
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter .java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelp er.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 hiberservlet.doPost(hiberservlet.java:159)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2422)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:163)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:199)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:700)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:584)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:536)
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:6240)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1557)
at oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:1528)
at org.hibernate.type.IntegerType.get(IntegerType.java:26)
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)
... 36 more

[/b]

[b]Name and version of the database you are using:oracle9i[/b]

[b]Procedure written as:
In Procedure :
CREATE OR REPLACE procedure SELECTPLAYERMASTER_SP2(vName out SYS_REFCURSOR,Vkey in varchar2)
AS
BEGIN
OPEN vName FOR
SELECT NAME FROM PLAYERTABLE ;

END ;
[/b]

[b]Debug level Hibernate log excerpt:[/b]

Hi,

I don't use Oracle but I think the syntax for calling a stored procedure in Oracle is {? = call SELECTPLAYERMASTER_SP2(?)}. I'm sure you can't mix positional (?) and named (:key) parameter markers.

Curtis ...

If i excute in connection.preparecall , it is executing well. Even if i removed one input keyand i have only one out parameter, and the procedure is as follows
[b]
CREATE OR REPLACE procedure selectPlayerMaster_SP4(vName OUT SYS_REFCURSOR)
IS
BEGIN
OPEN vName FOR
SELECT NAME FROM PLAYERTABLE;
END ;
[/b]
[b]when i am calling from java
Query query = session.getNamedQuery("selectPlayerMaster_SP");
List paymentMasterlist = query.list();
[/b]
[b]
in hbm.xml i have changed to
<property name="name" type="string" />
<property name="place" type="string" />
<property name="key" type="string" />
</class>
<sql-query name="selectPlayerMaster_SP" callable="true">
<return alias="player" class="player">
</return>
{ ? = call SELECTPLAYERMASTER_SP4()}
</sql-query>
[/b]
The following error occurs , if i have to pass inpput paramater then what to do with my previous code ? Pls help me out
[b]
could not execute query org.hibernate.exception.GenericJDBCException: could not execute query at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92) at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80) 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 hiberservlet.doPost(hiberservlet.java:177) at javax.servlet.http.HttpServlet.service(HttpServlet.java:760) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2422) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:163) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:199) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:700) at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:584) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683) at java.lang.Thread.run(Thread.java:536) Caused by: java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00306: wrong number or types of arguments in call to 'SELECTPLAYERMASTER_SP4' ORA-06550: line 1, column 7: PL/SQL: Statement ignored 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:582) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1141) at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2149) at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2032) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2894) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:684) 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) ... 36 more [b][list=][/list][/b]

Hi everyBody,
[b][color=red][/color][/b]
Still nOw i am unable to excute the stored Procedure , but the same stored procedure when written in function as follows
CREATE OR REPLACE FUNCTION PLAY_DETAIL RETURN SYS_REFCURSOR
AS
cust_recs SYS_REFCURSOR;
BEGIN
OPEN cust_recs FOR
SELECT * FROM PLAYERTABLE WHERE ROWNUM < 11;
RETURN cust_recs;
END;
/

and executed it is executed properly , but in case of stored procedure it gives same error as invalid argument , as given earlier.
So if any one has executed the stored procedure ,please tell me how to overcome the error .
I think Hibernate3.0 does not support Stored Procedure.

as stated in the docs the function or procedure has to return a resultset.

Under Oracle a function can't return that so a stored procedure is needed where as most other (more sane db's) can return a resultset and hence a stored function is needed.

if you are just executing methods to perform operations (hence not return entities) then you should use the connection directly.

Hi Max,

In my function it is returing a REF_CURSOR , and it is excuting properly and that returned cursor used as resultset in the servlet . but,but..
in case of stored procedure which is also returing ref_cursor as out parameter , : ORA-06550: line 1, column 13......as earlier

CREATE OR REPLACE procedure selectPlayerMaster_SP8(vName OUT SYS_REFCURSOR)
IS
BEGIN

OPEN vName FOR
SELECT * FROM PLAYERTABLE;

END ;
/
and in

player.hbm.xml
<sql-query name="selectPlayerMaster_SP7" callable="true">
<return alias="player" class="player">
<return-property name="id" column="ID" />
<return-property name="name" column="NAME" />
<return-property name="place" column="PLACE" />
<return-property name="key" column="KEY" />
</return>
{ ?=call SELECTPLAYERMASTER_SP8()}
</sql-query>

and in servlet :

Query query = session.getNamedQuery("selectPlayerMaster_SP7");
List paymentMasterlist = query.list();
int length=paymentMasterlist.size();

At the time of executing it is throwing error as in valid arument in procedure...
: ORA-06550: line 1, column 13 .. as earlier.

but running a function as same above it is executing properly with giving resultset as list.

So i dont think hibernae support stored procedure .
Please clarify my doubts.

so use a stored function; does it make a difference ?

Seriously - oracle is so bad with respect to jdbc handling of stored procs/function......