How to execute an Oracle function that returns a sys_refcursor using NHibernate?

Question

This is the function:

FUNCTION GET_ALL(P_USER_ID IN VARCHAR2) RETURN SYS_REFCURSOR IS
   C SYS_REFCURSOR;
BEGIN
    OPEN C
    FOR 'SELECT * FROM XYZ WHERE USER_ID = :P_USER_ID'
    USING P_USER_ID;

    RETURN C;
END;

I'm trying to call this function using NHibernate, like this:

Session
    .CreateSQLQuery("BEGIN ? = PKG.GET_ALL(:P_USER_ID); END;")
    .SetString("P_USER_ID", "SOMEONE")
    .List<XYZ>();

Any code, tips or smoke signs are welcome.

PS: I'm using NHibernate 3.3.0.GA

Possible duplicates: stackoverflow.com/a/4237147/232279 and stackoverflow.com/questions/5901638/… — ThinkJet
– ThinkJet, Commented Jun 25, 2013 at 3:58

Diego Mijelshon · Accepted Answer · 2013-06-25 12:35:27Z

1

From the official docs:

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.

There are working tests with full mapping and stored procedure code at https://github.com/nhibernate/nhibernate-core/tree/master/src/NHibernate.Test/SqlTest/Custom/Oracle

edited Jun 25, 2013 at 12:35

answered Jun 24, 2013 at 22:53

Diego Mijelshon

52.7k16 gold badges119 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonathas Costa Over a year ago

Yes, and my function returns a result set. And it uses sys_refcursor. But... the docs don't say how you put all these things together.

Diego Mijelshon Over a year ago

You should have an out parameter, not a return value. See added link.

Sash0k Over a year ago

@DiegoMijelshon really no way to use function that return sys_refcursor? I support some code and this pattern works for NHibernate 3.1 but doesnt work after update NHibernate

Collectives™ on Stack Overflow

How to execute an Oracle function that returns a sys_refcursor using NHibernate?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related