[b]Hibernate version:[3.0.5]

Why Hibernate with store procedure can't use with this procedure style.

I

CREATE OR REPLACE PROCEDURE "TMP_XXXX#SEARCH"

(

result_cursor OUT TYPES.cursor_type
)
AS

BEGIN
OPEN result_cursor FOR
SELECT tbl_XXX.id,
tbl_XXX.col_1,
tbl_XXX.col_2,
tbl_XXX.col_3,
tbl_XXX.col_4



FROM tbl_XXX;


END;
/


------------
ERROR--

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
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TMP_USP_RPT_RPT_S#SEARCH'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

......

<hibernate-mapping >

<class name="TBLXXX" table="TBL_XXX">

<id name="id" column="ID">
<generator class="native"/>
</id>

<property name="COL_1" column="COL_1"/>
<property name="COL_2" column="COL_2"/>
<property name="COL_3" column="COL_3"/>
<property name="COL_4" column="COL_4"/>


</class>

<sql-query name="selectAllXXX_SP" callable="true">
<return class="TBLXXX" >
<return-property name="id" column="ID"/>
<return-property name="COL_1" column="COL_1"/>
<return-property name="COL_2" column="COL_2"/>
<return-property name="COL_3" column="COL_3"/>
<return-property name="COL_4" column="COL_4"/>
</return>

{ ? = call TMP_XXXX#SEARCH() }
</sql-query>

</hibernate-mapping>

because it is not implemented. as stated in the docs the resultset must be *returned*.

In any case you can probably make it work by providing the proper binding in the Dialect.registerResultsetOutParameter()

I try to fill parameter name same this.

<sql-query name="selectAllXXX_SP" callable="true">
<return class="TBLXXX" >
<return-property name="id" column="ID"/>
<return-property name="COL_1" column="COL_1"/>
<return-property name="COL_2" column="COL_2"/>
<return-property name="COL_3" column="COL_3"/>
<return-property name="COL_4" column="COL_4"/>
</return>

{ call TMP_XXXX#SEARCH(?,:param_in) } <--------
</sql-query>


i wonder it work!! I use normal org.hibernate.dialect.Oracle9Dialect


but if I try to pass parameter.


{ call TMP_XXXX#SEARCH(?,:param_in1,:param_in2) } <--------

and when hibernate check for parameters, it reverst form down to up for example.

CREATE OR REPLACE PROCEDURE "TMP_XXXX#SEARCH"

(

result_cursor OUT TYPES.cursor_type,
param_in1 IN tbl_xxx.col_1%TYPE,
param_in2 IN tbl_xxx.col_2%TYPE
)
AS

BEGIN
OPEN result_cursor FOR
SELECT tbl_XXX.id,
tbl_XXX.col_1,
tbl_XXX.col_2,
tbl_XXX.col_3,
tbl_XXX.col_4



FROM tbl_XXX

where tbl_XXX.col_1 = param_in1;
AND tbl_XXX.col_2 = param_in2



END;
/

---------

and then I call it.

Query q = session.getNamedQuery("selectAllXXX_SP");
q.setParameter("param_in1","xxxx"); // param index [1]
q.setParameter("param_in2","xxxx"); // param index [0]

List resultList = q.list();

and It work,

So, but I would like to change parameter index for
result_cursor OUT TYPES.cursor_type,

to param index [0].

Regards,
Suraphat Siri-upatham
Thailand.