Hi,

I am using Hibernate + Spring. I want to retrieve the records from DEPT table using stored procedure.

Here is the stored pro:

CREATE OR REPLACE PACKAGE types
AS
TYPE ref_cursor IS REF CURSOR;
END;

CREATE OR REPLACE PROCEDURE GET_DEPT_PROCEDURE (
P_DEPTS OUT types.ref_cursor
)
AS
BEGIN
OPEN P_DEPTS FOR
SELECT
deptno as Id,
dname as namekey,
loc as Datekey FROM dept ;
END ;

And in Hibernate Mapping file this is the named query tag...

<sql-query name="name3" callable="true">
<return alias="DeptObj" class="secondEx.DeptObj"/>
{ call GET_DEPT_PROCEDURE() }
</sql-query>

Where DEPTOBJ is the POJO corresponding to DEPT table..


And in Retrieve button Action performed I am calling the following method.....


public ArrayList getAllDeptDtls(){
return

(ArrayList)getHibernateTemplate().findByNamedQuery("name3");


}


When I run this, I am getting the following error:

There is a exception in Retrieve Method....Hibernate operation: could not execute query; invalid ResultSet access for SQL [{ call GET_DEPT_PROCEDURE() }];

nested exception is java.sql.SQLException: Invalid column index
31735 [AWT-EventQueue-0] WARN util.JDBCExceptionReporter - SQL Error: 17003, SQLState: null

31735 [AWT-EventQueue-0] ERROR util.JDBCExceptionReporter - Invalid column index...

Thanx in advance

Hi,
did you look at this:

http://www.hibernate.org/hib_docs/v3/re ... procedures

and especially that quote



Maybe this helps.

Hi,

Thanx for the reply.....

Now I tried with,

call GET_DEPT_PROCEDURE(rr SYS_REFCURSOR) and also

call GET_DEPT_PROCEDURE(rr types.ref_cursor)

... but getting the same error

Hi,

Now its working.. I tried with

<sql-query name="name3" callable="true">
<return alias="DeptObj" class="secondEx.DeptObj">
<return-property name="deptno" column="DEPTNO"/>
<return-property name="dname" column="DNAME"/>
<return-property name="location" column="LOC"/>
</return>
{ call GET_DEPT_PROCEDURE(?) }
</sql-query>

Thanx a lot....