I am working on an application using NHibernate 2.0GA. It’s partially working with the two available drivers: NHibernate.Driver.OracleDataClientDriver and NHibernate.Driver. OracleClientDriver. Following the discrete samples found on the web, I tried the following alternative configurations:

<!--Use OracleDataClientDriver-->
<!--<property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>
<property name="connection.connection_string">Data source=progd1;User Id=***;Password=***;</property>-->

And:
<!--Use OracleClientDriver-->
<property name="connection.driver_class">NHibernate.Driver.OracleClientDriver</property>
<property name="connection.connection_string">Data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=1521)))(CONNECT_DATA=(SID = progd1)));User Id=***;Password=***;</property>

With the first configuration I can load the data from database. But cannot save data. I feel the NHibernate is not generating the correct insert statement understood by the driver. See my earlier post: http://forum.hibernate.org/viewtopic.php?t=990795.

With the second configuration I cannot even load the data. Seems the driver cannot even understand the generated select statement. Here is the generated sql:

SELECT workflowin0_.WORKFLOWINDEXID as WORKFLOW1_32_0_, workflowin0_.[NAME] as column2_32_0_, workflowin0_.[DESCRIPTION] as column3_32_0_ FROM WORKFLOWINDEX workflowin0_ WHERE workflowin0_.WORKFLOWINDEXID=?

And it throws exception "ORA-01747: invalid user.table.column, table.column, or column specification".

When I paste this select statement on SQLPlus and replace the ‘?’ mark with parameter, I get exactly the same error.

So, what are the right driver and configuration for accessing Oracle 10g, using NHibernate 2.0GA? Did anyone successfully used this version for Oracle?

Here is the complete configuration section in my app.config:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<bytecode-provider type="lcg"/>
<reflection-optimizer use="true"/>
<session-factory name="NHibernate.Default">
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>

<!--Use OracleDataClientDriver-->
<!--<property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>
<property name="connection.connection_string">Data source=progd1;User Id=***;Password=***;</property>-->

<!--Use OracleClientDriver-->
<property name="connection.driver_class">NHibernate.Driver.OracleClientDriver</property>
<property name="connection.connection_string">Data source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=1521)))(CONNECT_DATA=(SID = progd1)));User Id=***;Password=***;</property>

<property name="show_sql">true</property>
<property name="dialect">NHibernate.Dialect.Oracle9Dialect</property>
<property name="use_outer_join">true</property>
<property name="command_timeout">444</property>
<property name="query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
<property name="adonet.wrap_result_sets">false</property>

<mapping assembly="ProgressiveNhEdm"/>
</session-factory>
</hibernate-configuration>