Sony Arouje

I am working on a project where I have to connect to an Oracle database. I used NHibernate as my ORM, every thing worked as expected until the Save operation. The table I was dealing with has a ROWID field an auto generated field by Oracle. Here we cannot use the native generator of NHibernate, I felt the best fit here is ‘trigger-identity’ generator. As shown below I configured the rowid in my hbm file.

    <id name="_rowId" column="RowId" access="field">
      <generator class="trigger-identity"></generator>
    </id>

To my surprise Nunit throws the error “System.Exception : Parameter ‘:nhIdOutParam’: No size set for variable length data type: String.” whenever I try to save the entity.

I analyzed the insert query generated by NHibernate and every things is fine. Next option for me is google, like every one else I started searching in google, I got so many forums where people reported this issue but no solid answer. The error stack trace shows that the error is from System.Data.OracleClient, I felt like the issue is with the Oracle client I am using, here I am using the client from Microsoft not the native ODP.Net provided by Oracle. It seems like system.data.OracleClient is unable to handle the ‘returning’ statement added by NHibernate.

Nhibernate will create the Insert statement as shown below

Insert into Table (filed1, field2) values (p1, p2) returning RowId into :nhIdOutParam

I changed the NHibernate configuration to use the Oracle client and referred Oracle.DataAccess.dll to my project. Compiled the project and run the NUnit test, wow I got the Green bar after 4hrs of struggling. In my previous NHibernate configuration (hibernate.cfg.xml) the driver class property was

<property name="connection.driver_class">NHibernate.Driver.OracleClientDriver</property>

This configuration forces NHibernate to use the Oracle client provided with .NET. To instructed NHibernate to use the native oracle assembly (Oracle.DataAccess.dll) change the configuration as below

<property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>

Hope this post may help some one to save some time.