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.