Hi All: I have seen several postings in the forums on Stored Procedures. However, i am still having problems executing the stored procedure. I am getting the follwing exception when I try to run the named query..

***************************************************************
* named native SQL query invoiceuser.selectAllInvoiceUsers_SP */ { ? = call selectAllInvoiceUsers() }
[11/12/06 14:33:22:025 CST] 00000060 JDBCException W org.hibernate.util.JDBCExceptionReporter logExceptions SQL Error: 900, SQLState: 42000
[11/12/06 14:33:22:041 CST] 00000060 JDBCException E org.hibernate.util.JDBCExceptionReporter logExceptions ORA-00900: invalid SQL statement

[11/12/06 14:33:22:056 CST] 00000060 SystemOut O could not execute query

**********************************************************

My Stored procedure is:

*********************************************************
create or replace FUNCTION selectAllInvoiceUsers
RETURN SYS_REFCURSOR
AS
st_cursor SYS_REFCURSOR;
BEGIN
OPEN st_cursor FOR
SELECT INVC_USER_ID, USER_ID, FIRST_NAME, LAST_NAME FROM INVOICE_USER;
RETURN st_cursor;
END;
*********************************************************

My mapping files contains the following...

*********************************************************

<sql-query name="invoiceuser.selectAllInvoiceUsers_SP" callable="true">
<return-scalar column="invc_user_id" type="long"/>
<return-scalar column="user_id" type="string"/>
<return-scalar column="first_name" type="string"/>
<return-scalar column="last_name" type="string"/>

{ ? = call selectAllInvoiceUsers() }
</sql-query>

********************************************************

Can someone let me know what I am missing?

Thanks in advance

Raj Sankuratri

Hi,

There is information in the H3 manual regarding stored procedures -- see section 16.2.2.1

link is
http://www.hibernate.org/hib_docs/v3/re ... rysql.html

This link provides standard ways of calling stored proc in Hibernate...

try like this... { call selectAllInvoiceUsers(?) }

Nope... That will not work either (I did check it though). Actually, I cannot put the question mark inside the parenthesis because there are no arguments.

Anyone with other ideas?

Thanks

Raj

Hi,

The question mark inside the method indicates OUT parameter....which holds return result set.....