Nhibernate Could not Execute Query - Oracle Procedure returning refcursor

Question

my package contains the following procedure -->

Package body myNewack as Procedure DisplayEmployees(Emplist OUT refCursor) Is begin open Emplist for select EmpId, FirstName, LastName from employee; end; end myNewPack;

my hbm mapping file contains the following named query -->

<sql-query name="mytestsp" callable="true">
 call MYNEWPACK.DISPLAYEMPLOYEES()
</sql-query>

and from .net i'm using the following code to call this procedure

public IList<NhbHelper.Employee> GetEmployees()
{
  IList<NhbHelper.Employee> myList;
  ISession session = NHibernateHelper.GetCurrentSession();
  var query= session.GetNamedQuery("mytestsp");
  return query.List<NhbHelper.Employee>();
}

Whenever i'm executing this code i'm getting error "Could not execute query [ call MYNEWPACK.DISPLAYEMPLOYEES()] [SQL:call MYNEWPACK.DISPLAYEMPLOYEES()]"

Kindly help me fix this issue, how can i execute the oracle procedure that returns refcursor

Community · Accepted Answer · 2017-05-23 10:09:12Z

0

I guess you're missing the return definition in your hbm mapping file.
It should be something like that:

<sql-query name="mytestsp" callable="true">
    <return class="NhbHelper.Employee, NhbHelper" />
    call MYNEWPACK.DISPLAYEMPLOYEES()
</sql-query>

I struggled with same issue few months ago. I was using PACKAGES and I came up with this solution.

If you want to find out more you can read a good article which helped me a lot.

UPDATE:

You can mix HBM and fluent mappings.

edited May 23, 2017 at 10:09

CommunityBot

11 silver badge

answered May 30, 2012 at 8:04

LeftyX

35.6k21 gold badges140 silver badges201 bronze badges

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

2 Comments

Tarak Over a year ago

Hi LeftyX: But I'm not using Fluent NHibernate classes, so is there any other way of doing this, let me know

LeftyX Over a year ago

@Tarak: I've updated my answer. You can mix HBM and fluent mapping. If my answer is good enough for you, you can mark it as "asnwered" and even up-vote ;-)

Collectives™ on Stack Overflow

Nhibernate Could not Execute Query - Oracle Procedure returning refcursor

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related