After debuging for a few hours I’ve found the proper way of calling an Oracle function from hibernate and to use the return parameters. Here is the solution.
The Oracle SQL function which returns -1 or 0:
<pre lang="sql">create or replace function checkLastNPass
(userId IN number, ... other params)
RETURN SYS_REFCURSOR
is
st_cursor SYS_REFCURSOR;
prevId number:= userId;
... other scope variables
begin
...
OPEN st_cursor FOR select -1 as retVal from DUAL;
return st_cursor;
...
OPEN st_cursor FOR select 0 as retVal from DUAL;
return st_cursor;
end;
/
The important bits are:
- you cannot return a scalar but must always return a SYS_REFCURSOR
- it is not mandatory that there is a first parameter of type OUT and SYS_REFCURSOR (the doc is a bit unclear on this topic)
- if returning scalars you must alias them
The **mapping**:
```
{ ? = call checklastnpass(:userId, ...) }
```
Note that the alias is used here to identify the return value. I first tried using column=”?” but this resulted in:
could not read column value from result set: ?; Invalid column name
The code:
<pre lang="java">Query query = hsession.getNamedQuery("CheckLastNPass");
query.setParameter("userId", user.getId());
...
result = (Integer)query.uniqueResult();
Applies to Hibernate 3.x.