ansaurus

Question

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

Answer 1

+1 A:

callable = true is for calling stored procedures with {? = call ...()} syntax. Oracle's select ... from dual syntax is a normal query, so you don't need callable = true:

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

axtavt 2010-05-31 13:09:54

Ahhh, interesting. I wasn't aware of that (if this works).

Pascal Thivent 2010-05-31 13:13:21

I tried your suggestion, please see my update.

Péter Török 2010-05-31 14:03:19

Answer 2

+2 A:

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.

Pascal Thivent 2010-05-31 13:11:26

I read that too, but I am not sure either, since in a different part of the book (ch 8.2.2) there is a section about calling stored functions (however, there is no straightforward example :-( ). And the forum thread I found suggests that it is indeed possible using `getNamedQuery()`...

Péter Török 2010-05-31 13:23:43

@Péter Indeed, and axtavt's answer goes in the same direction. I'm eager to see if it works.

Pascal Thivent 2010-05-31 13:44:34

FYI, in the end it turned out the Hibernate documentation is right in this case, so I had to use JDBC.

Péter Török 2010-06-01 12:46:03

@Péter Thanks for the feedback.

Pascal Thivent 2010-06-01 21:32:20

Answer 3

+1 A:

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);

Péter Török 2010-06-01 12:43:51

+1 for posting the actual solution

Pascal Thivent 2010-06-01 21:32:50

ansaurus

tags:

views:

answers:

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

16.2.2. Using stored procedures for querying

16.2.2.1. Rules/limitations for using stored procedures

related questions