Hibernate: how to call a stored function returning a varchar?

Question

I am trying to call a legacy stored function in an Oracle9i DB from Java using Hibernate. The function is declared like this:

create or replace FUNCTION Transferlocation_Fix (mnemonic_code IN VARCHAR2)
   RETURN VARCHAR2

After several failed tries and extensive googling, I found this thread on the Hibernate forums which suggested a mapping like this:

<sql-query name="TransferLocationFix" callable="true">
    <return-scalar column="retVal" type="string"/>
    select Transferlocation_Fix(:mnemonic) as retVal from dual
</sql-query>

My code to execute it is

    Query query = session.getNamedQuery("TransferLocationFix");
    query.setParameter("mnemonic", "FC3");
    String result = (String) query.uniqueResult();

and the resulting log is

DEBUG (org.hibernate.jdbc.AbstractBatcher:366) -  - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG (org.hibernate.SQL:401) -  - select Transferlocation_Fix(?) as retVal from dual
TRACE (org.hibernate.jdbc.AbstractBatcher:484) -  - preparing statement
TRACE (org.hibernate.type.StringType:133) -  - binding 'FC3' to parameter: 2
TRACE (org.hibernate.type.StringType:133) -  - binding 'FC3' to parameter: 2

java.lang.NullPointerException
at oracle.jdbc.ttc7.TTCAdapter.newTTCType(TTCAdapter.java:300)
at oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCColumnArray(TTCAdapter.java:270)
at oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCDataSet(TTCAdapter.java:231)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1924)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2599)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:736)
at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.execute(NewProxyCallableStatement.java:3044)
at org.hibernate.dialect.Oracle8iDialect.getResultSet(Oracle8iDialect.java:379)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:193)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1784)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811)
at com.my.project.SomeClass.method(SomeClass.java:202)
...

Any clues what am I doing wrong? Or any better ways to call this stored function?

---

Update: upon trying @axtavt's suggestion, I get the following error:

ORA-14551: cannot perform a DML operation inside a query

The function indeed does extensive inserts/updates, so I guess the only way to run it would be using the stored procedure syntax. I just have no clue how to map the return value:

<sql-query name="TransferLocationFix" callable="true">
    <return-scalar column="???" type="string"/>
    { ? = call Transferlocation_Fix(:mnemonic) }
</sql-query>

What should be the column? I will try an empty value...

---

Update2: that failed as well, with an SQL Grammar Exception... So I tried the JDBC way as suggested by Pascal, and it seems to work! I added the code in an answer below.

Answer 1

For further reference, here is my final solution:

CallableStatement statement = session.connection().prepareCall(
        "{ ? = call Transferlocation_Fix(?) }");
statement.registerOutParameter(1, Types.VARCHAR);
statement.setString(2, "FC3");
statement.execute();
String result = statement.getString(1);

Answer 2

I am not 100% sure and I didn't test it but according to Hibernate's documentation:

16.2.2. Using stored procedures for querying

Hibernate3 provides support for queries via stored procedures and functions. Most of the following documentation is equivalent for both. The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate. An example of such a stored function in Oracle 9 and higher is as follows:

CREATE OR REPLACE FUNCTION selectAllEmployments
    RETURN SYS_REFCURSOR
AS
    st_cursor SYS_REFCURSOR;
BEGIN
    OPEN st_cursor FOR
 SELECT EMPLOYEE, EMPLOYER,
 STARTDATE, ENDDATE,
 REGIONCODE, EID, VALUE, CURRENCY
 FROM EMPLOYMENT;
      RETURN  st_cursor;
 END;

To use this query in Hibernate you need to map it via a named query.

<sql-query name="selectAllEmployees_SP" callable="true">
    <return alias="emp" class="Employment">
        <return-property name="employee" column="EMPLOYEE"/>
        <return-property name="employer" column="EMPLOYER"/>
        <return-property name="startDate" column="STARTDATE"/>
        <return-property name="endDate" column="ENDDATE"/>
        <return-property name="regionCode" column="REGIONCODE"/>
        <return-property name="id" column="EID"/>
        <return-property name="salary">
            <return-column name="VALUE"/>
            <return-column name="CURRENCY"/>
        </return-property>
    </return>
    { ? = call selectAllEmployments() }
</sql-query>

Stored procedures currently only return scalars and entities. <return-join> and <load-collection> are not supported.

16.2.2.1. Rules/limitations for using stored procedures

You cannot use stored procedures with Hibernate unless you follow some procedure/function rules. If they do not follow those rules they are not usable with Hibernate. If you still want to use these procedures you have to execute them via session.connection(). The rules are different for each database, since database vendors have different stored procedure semantics/syntax.

Stored procedure queries cannot be paged with setFirstResult()/setMaxResults().

The recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. Native call syntax is not supported.

For Oracle the following rules apply:

• A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type. See Oracle literature for further information.

...

As I said, I'm not sure but my understanding is that you'll have to use session.getConnection() here.