Call Oracle procedure from hibernate

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

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:

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

 
Applies to Hibernate 3.x.

Related Posts with Thumbnails
Be Sociable, Share!

Leave a Reply

*