Oracle stored procedures, SYS_REFCURSOR and NHibernate

Question

I have a legacy Oracle (10.2g) database that I'm connecting to and I'd like to use NHibernate (2.0.1) to give me back objects from a stored procedure. The stored procedure in question uses a SYS_REFCURSOR to return results. According to the documentation this should be doable but I've found a few posts on the internet that suggest otherwise.

Here's my paraphrased code:

Mapping file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" 
assembly="OracleStoredProcedures" namespace="OracleStoredProcedures">
    <class name="Person" mutable="false">
     <id name="PersonCode" type="AnsiString" column="PERSONCODE">
      <generator class="assigned" />
     </id>
     <property name="Name" type="String" column="PERSON_NAME" />
     <property name="Surname" type="String" column="PERSON_SURNAME" />
    </class>

    <sql-query name="getpeople">
     <return class="Person" />

     EXEC RS_DB.GETPERSONTEST 

    </sql-query>
</hibernate-mapping>

Stored procedure:

CREATE OR REPLACE PROCEDURE RS_DB.GETPERSONTEST (
   io_cursor   IN OUT   sys_refcursor
)
IS
BEGIN
   OPEN io_cursor FOR
      SELECT PERSONCODE, PERSON_NAME, PERSON_SURNAME
      FROM PEOPLE

END GETPERSONTEST;

Answer 1

According to this page you should use CALL instead of EXEC. I have not tried this, so YMMV.

Answer 2

In your hibernate you state a return type, but Oracle procedures don't return anything. Perhaps if you changed it to a function which returned the ref cursor it would work properly. Also, I believe CALL is the proper syntax. EXEC is a SQL*Plus command and not really a SQL statement.

Answer 3

As far as I remember this was a bug I also found a couple of years ago when working with oracle, I've tracked back the issue in NH tracker and is fixed but on version 2.1.1GA; Can you verify that this is the same problem you have? http://216.121.112.228/browse/NH-847

Hope it helps;

Seba