Hello,
I am trying to call the following stored function from my code using hibernate:

FUNCTION setUserObjectIDACL (
u_usertype IN number,
u_userid IN varchar2,
o_objectsystem IN number,
o_objecttype IN number,
o_objectid IN varchar2,
o_newallow IN number,
o_newdeny IN number,
o_child IN number := 0) RETURN number
IS
................
BEGIN
..................
..................
..................
-- return the allow value set
RETURN o_setallow;
END setUserObjectIDACL;


This is how I am defining it in my mapping file:

<sql-query name="setUserObjectIDACL" callable="true">
<return-scalar column="o_setallow" type="int" />
{ ? = call CCC_PROC.setUserObjectIDACL(?, ?, ?, ?, ?, ?, ?)}
</sql-query>


This is how my Java code calls it:

Query namedQuery = hSession.getNamedQuery("setUserObjectIDACL")
.setInteger(0, temp)
.setString(1, userID)
.setInteger(2, objectSystem)
.setInteger(3, objectType)
.setString(4, objectID)
.setInteger(5, allowValue)
.setInteger(6, denyValue);
Integer id = (Integer) namedQuery.uniqueResult();


But I am getting the following error:

15:55:10,861 ERROR [JDBCExceptionReporter] ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

15:55:10,861 ERROR [ACLUtilities] Exception while executing stored procedure setUserObjectIDACL
15:55:10,861 ERROR [STDERR] org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)



I'll really appreciate if anyone lets me know if I am doing anything incorrectly.

Thanks,
Bhuwan.

From the reference manual:

16.2.2.1. Rules/limitations for using stored procedures
To use stored procedures with Hibernate the procedures/functions have to
follow some rules. If they do not follow those rules they are not usable with Hibernate. If you still want to use these procedures you have to execute them via session.connection(). The rules are different for each database, since database vendors have different stored procedure semantics/syntax.
Chapter 16. Native SQL
238 Hibernate 3.3.1
Stored procedure queries can't be paged with setFirstResult()/setMaxResults().
Recommended call form is standard SQL92: { ? = call
functionName(<parameters>) } or { ? = call procedureName(<parameters>}.
Native call syntax is not supported.
For Oracle the following rules apply:
• A function must return a result set. 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.

hope that helps

Patrik

Thanks for your answer Patrik. I am able to run it successfully using hSession.connection(), But I wanted to have it working thru Hibernate. Too bad to see that hibernate has such limitations with Stored Procs/Functions.
Thanks a lot anyways.

P.S.: Not to mention that session.connection() method has been deprecated.

Hi bhuwanpatel,

I am having the same problem.
How did you make it work? Could you post your code?

Thanks in advance.

Tuno

Tuno,
I am using hSession.getConnection and then using that connection to work with stored procedures and functions.
I couldnt find a better solution.