I'm facing some issues whenever i invoke oracle stored procedures.

Please let me know whether it supports oracle stored procedures.

Without stored procedures I could carry out CRUD Functionality. But I want to use stored procedures.

By default, command type is Text and the parameter names are p0, p1, p2 and so on. If I change the values of command type and parameter name at run time I could invoke oracle stored procedures.

Thanks in advance. Waiting for your feedback at the earliest.

I don't have Oracle installed so I can only provide a few guesses based on Internet searches. Try this:

The usual restrictions apply - the number of parameters must match the number of columns, the parameters must be in order NHibernate expects them, etc.

The statement and the order of the parameters are fine. If there are any issues in those areas I could not invoke the stored procedure after I have changed the commandtype to storedprocedure and the parameter names at run time (Using QuickWatch).

By default the commandtype is taking as CommandType.Text and the parameter names are taking as p0, p1 and so on. The problem lies in this area only. To support oracle stored procedure the commandtype should be storedprocedure and the parameter names should be as per the mapping file.

Is there any option in the mapping or config file to say that it should change the commandtype and the parameter name instead of default values?

Thanks in advance.

No, there isn't any such option, because it was too complex to implement at the time.

The problem i was facing while invoking the stored procedure has been fixed. For oracle, call keyword should be used to invoke the procedure and the parameter names should be given in bracket and oracle.dataaccess should be used instead of microsoft oracle driver. Thanks a lot for your response.

Please help me out from the following issue.

Find below the stored procedure, mapping file and the class file. In the GETBIll stored procedure, i'm returning refcursor as output and billid is my input parameter.
I have given the output parameter as the first parameter as per rules. I want to know whether i'm calling the procedure in a right way in the mapping file as well as i would like to know how to invoke this procedure in front end.

Stored Procedure
CREATE OR REPLACE PROCEDURE GETBILL(
RETVAL OUT SYS_REFCURSOR,
BillID IN BILL_POC.BILL_ID%type
)
IS
BEGIN
OPEN RETVAL FOR 'SELECT BILL_DESC,STARTDATE FROM BILL_POC WHERE BILL_ID = :0' using BillID;

END GETBILL;
/

Mapping file

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns:xsd='http://www.w3.org/2001/XMLSchema'
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
xmlns='urn:nhibernate-mapping-2.0'
namespace="NHibernatePOB" assembly="NHibernatePOB" default-lazy="false">
<class name="NHibernatePOB.Bill" table="BILL_POC">
<id name="BillID" type="Int32" unsaved-value="null">
<column name="BILL_ID" sql-type="Integer" not-null="true" unique="true" />
<generator class="assigned" />
</id>
<property name="BillDesc" type="String">
<column name="BILL_DESC" length="20" sql-type="VARCHAR2" not-null="true" />
</property>
<property name="BillDate" type="DateTime">
<column name="STARTDATE" sql-type="DATE" not-null="true"/>
</property>
<loader query-ref="GETBILL"/>

<sql-insert >
Call INSERTBILL (:BillDesc,:BillDate,:BillID)
</sql-insert>

<sql-update>
Call UPDATEBILL (:BillDesc,:BillDate,:BillID)
</sql-update>

<sql-delete>
Call DELETEBILL (:BillID)
</sql-delete>
</class>

<sql-query name="GETBILL">
<return class="NHibernatePOB.Bill">
<return-property name="BillDesc" column="BILL_DESC"></return-property>
<return-property name="BillDate" column="STARTDATE"></return-property>
</return>
Call GETBILL (:Bill,:BillID)
</sql-query>
</hibernate-mapping>

Class file
using System;
using System.Collections.Generic;
using System.Text;

namespace NHibernatePOB
{
public class Bill
{
private int _BillID;

public int BillID
{
get { return _BillID; }
set { _BillID = value; }
}
private string _BillDesc;

public string BillDesc
{
get { return _BillDesc; }
set { _BillDesc = value; }
}
private DateTime _BillDate;

public DateTime BillDate
{
get { return _BillDate; }
set { _BillDate = value; }
}
}
}

Sorry, I don't have much experience with Oracle so I can't tell whether it's right or not. In general, your procedure should behave just like a SELECT statement from the perspective of ADO.NET. That is, creating an IDbCommand, setting its CommandText to "Call GETBILL (:Bill,:BillID)" and calling ExecuteReader() should return an IDataReader with the necessary results.

I have changed the sql-query syntax as follows
<sql-query name="GETBILL">
<return class="NHibernatePOB.Bill" >
<return-property name="BillID" column="BILL_ID" ></return-property>
<return-property name="BillDesc" column="BILL_DESC"></return-property>
<return-property name="BillDate" column="STARTDATE"></return-property>
</return>
:RETVAL = Call GETBILL (:BillID)
</sql-query>

and try to invoke the procedure as follows.
Bill obj1 = new Bill();
obj1.BillID = 2;
Bill obj = new Bill();
using (ISession session = PersistenceManager.NHSession)
{
IQuery qr = (IQuery) session.GetNamedQuery "GETBILL");
qr.SetParameter("BillID",obj1.BillID);
qr.SetParameter("RETVAL", obj);
qr.List<Bill>();
}

When i look into the command object at run time, command text has been changed like {:p0} = Call GETBILL ({:p1}) and both parameter directions are input. In my procedure RETVAL is the output parameter and BillID is the input parameter. Can i use IQUERY to invoke the stored procedure . Is there any option to specify the parameter direction and pararameter type.

Is it possible to call a stored procedure having both input and output parameter?

No, it's not possible to use OUT parameters. Again, the procedure must behave exactly as NHibernate-generated SQL would.

Is there any other way i could return the result set without using OUT param. Please help me out.

I'm using IQuery and limited the pagesize to fetch the data.
ISession session = PersistenceManager.NHSession;
IQuery qr1 = session.CreateQuery("From Bill");
qr1.SetMaxResults(2);
qr1.SetFirstResult(1);
IList<Bill> lst = qr1.List<Bill>();

But i'm getting the following error. Please help me out. I'm using nHibernate version 1.2 , .net 2.0 and oracle database. There is no stored procedure. Directly trying to fetch the database.

{"Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name: index"}

Please correct my following understanding.

SetMaxResult is used to set the page size. Suppose I'm giving 10 it will display only records per page. SetFirstResult is used to specify the row no. from which we need to fetch the record. Suppose i'm giving 1, it will fetch 1-10 records and if i change the value of setfirstresult to 11, it will fetch from 11-20. Pls. correct me if I’m wrong.

Check this out http://forum.hibernate.org/viewtopic.php?t=967268&highlight=maxresults dont know if its related

/Floda

In my project I have to call the stored procedures via nhibernate and all the procedures are having OUT parameters. Can you suggest me what I can do and is it possible to fix in 1.2 realease. When is version 1.2 getting released? Waiting for your feedback at the earliest.

I second that

In Beta2 when Isession gets activated , transaction will be enabled but not in Beta1. Please correct me If i'm wrong.