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:

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

<sql-query name="CheckLastNPass" callable="true">
    	<return-scalar column="retVal" type="java.lang.Integer"/>
    	    { ? = call checklastnpass(:userId, ...) }
</sql-query>

could not read column value from result set: ?; Invalid column name

Query query = hsession.getNamedQuery("CheckLastNPass");
query.setParameter("userId", user.getId());
...
result = (Integer)query.uniqueResult();

Be Sociable, Share!

•