Tuesday, January 16, 2007

As long as ODP.NET is under the hood - NHibernate

A lot of developers are embracing ODP.NET as their Oracle ADO.NET provider (this as opposed to the built in Microsoft OracleClient).
ODP.NET is a high performance provider that takes advantage of Oracle database functionality. More than that, ODP.NET is fully documented, and updates regularly.

When working with NHinbernate, we can still make sure that it's using ODP.NET under the hood. This will allow us to utilize ODP.NET features like the Connection Pool, Tracing etc. Plus, since this product is at Oracle's hands, it will ensure us compatibility and optimization with the Oracle database.

In the NHibernate config file, use this driver_class:

<property name="connection.driver_class">

Can it be easier than that?


Martin said...

Just trying to get this working (NHibernate with ODP.NET Driver) and am getting exceptions about the location of Oracle.DataAccess regardless it seems of where I put it, any ideas ?

Tomer Avissar said...

Hi Martin, I’m guessing you are working with NHibernate 1.2 which this error is common, because the driver assembly is no longer loaded from the GAC with Assembly.LoadWithPartialName(), but with Assembly.Load(). In this case you can place Oracle.DataAccess.dll in your bin directory or use the more recommended way which is to add a qualifyAssembly section into your app.config/web.config. Something like this:

<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<qualifyAssembly partialName="Oracle.DataAccess"
fullName="Oracle.DataAccess, Version=, Culture=neutral, PublicKeyToken=89b483f429c47342" />

Todd said...

I added the appropriate qualifyAssembly configuration to my web.config but I still cannot get the app to find the Oracle.DataAccess.dll in the GAC. Any thoughts on what I might be missing?

Tomer Avissar said...

First check that the you specified the save version as the one installed in your gac.
If that doesn't help I would check with FileMon (you can download from http://www.microsoft.com/technet/sysinternals/SystemInformation/Filemon.mspx) and make sure the app.config file is being called, and that your application finds Oracle.dataaccess file.

Sergey said...

Hi! I'm trying to connect Oracle using ODP from NHibernate. But I have next exception:
ORA-12154: TNS:could not resolve the connect identifier specified

My app.config:
"hibernate.connection.provider = NHibernate.Connection.DriverConnectionProvider
hibernate.dialect = NHibernate.Dialect.Oracle9Dialect
hibernate.connection.driver_class = NHibernate.Driver.OracleDataClientDriver
hibernate.connection.connection_string = Data Source=jam@test.ad.ru;User ID=jam;Password=jam;
hibernate.connection.isolation = ReadCommitted
hibernate.cache.use_minimal_puts = true
hibernate.cache.use_query_cache = true
hibernate.show_sql = true
hibernate.cache.use_second_level_cache = true
hibernate.cache.provider_class = NHibernate.Caches.SysCache.SysCacheProvider, NHibernate.Caches.SysCache"

If i'm try to connect using windows provider all is Ok. But NHibernate made wrong sql.

Tomer Avissar said...

Hi Sergey,
I believe your connection string is wrong. The data source attribute should be without the username like so:
Data Source=test.ad.ru;User ID=jam;Password=jam;

Sergey said...

I'm trying different Data Sources, but result all time one :(

JaM said...

I have next trouble:

{"ORA-12154: TNS:could not resolve the connect identifier specified"}

Tomer Avissar said...

Hi Jam,

Usually this error means that ODP.NET does not recognize your data source. Check your tnsnames.ora file located in the oracle_home\NETWORK\ADMIN folder.

markov said...

I am using the oracle ODP for some time. The problem is that a oracle client needs to be installed on the client side, and therefore there is no way to know which version the client installs or not. What is the best way for hibernate do avoid bounding to a specific version of the dataaccess.dll?

ilker ozen said...

Hi, I am trying to connect to Oracle with Hibernate but I get the error message below whenever I try.

NHibernate.HibernateException: The hibernate.connection.driver_class must be specified in the NHibernate configuration section

What might be wrong ?

Tomer Avissar said...

Hi Markov,
There is actually a new feature of ODP that may help you.
In 11g (currently in a beta) there is a feature called Instant Client that basically means that you can distribute the ODP assembly along with the other Oracle client dlls, and that way not depend on the client's own Oracle client.

Markov said...

Thank you for the reply.
The mayor problem of the instant client is its size (which makes the though decision if one should ship the instant client with the own software release). Do you have any idea how big the instant client is going to be in this beta release?

Markov said...

Well I have downloaded the instant client 11 beta, and the required client dll is 107Mb big! Compressed makes it with all dll's ca. 25Mb. When do they learn?

Markov said...

Maybe you could help me on this. I am using the beta instant client 11b (at least trying to use). I have referenced the data access dll from my project and copied the instant client files to the executing directory. After tying to connect to the oracle server i am getting following exception:

NHibernate.ADOException: cannot open connection ---> Oracle.DataAccess.Client.OracleException ORA-12705: Cannot access NLS data files or invalid environment specified...

The server is running on another machine and is very well accessed by other tools or when using the old data acces dll and normal client.

The files used:
Oracle.DataAccess.dll (referenced)

Big tnx in advance.

Tomer Avissar said...

Hi Markov,

Like you said the instant client is ~100 Mb, and although it does not require any registry settings it can still be affected by it.
Please check the value for NLS_LANG in the HKLM\Software\Oracle\[Oracle_Home]. You can try deleting the value altogether.

Minko Minkov said...

Hello Tomer,
I have problem when use ODP.NET + Nhibernate and tried to insert/update chinese symbols. Every thing is ok when I use Microsoft oracle clien driver.

Any idea what is wrong?

Tomer Avissar said...

This depends on your database encoding. In a UTF-8 database (I assume UTF-8 includes chinese symbols) this should work, but if you are using a non Unicode encoding database with a nvarchar2 column, you might get this problem because NHibernate does not know to use the ODP.NET nvarchar2 type. If this is indeed the case, you can create your own driver that inherits from the ODP driver class (OracleDataAccessClient) and implement the nvarcchar2 support.

Anonymous said...

Hi there to every single one, it's in fact a fastidious for me to visit this web page,
it includes precious Information.