MS OracleClient driver vs ODP.NET driver problem

Topics: Data Access Application Block
Jan 2, 2007 at 11:07 AM

I'm currently working on a project and we are busy with the migration from entlib 1.1 to entlib 2.0.

In the entlib 1.1 some other team members and i made changes in the entlib data block. The majer modification was the switch from the MS Oracle driver to the ODP.NET driver. All went very well but in the migration process we still have on big issue.

In ODP.NET the OracleParameter exposes the "CollectionType" property to get or set a OracleCollectionType enum value. One of the enum values "PLSQLAssociativeArray" and by setting this value we can pass an array of values as parameter value (ed an array of ids, strings, ...). The Oracle packages accept this array and fills a temp table in the DB and creates a join in the select query between the table which will be quaried and the temp table. This gives something like this: "select id, name, ... from tableX where id = (select distinct(id) from temptable)".

As far as i know the MS driver doens't support the passing of Arrays... Does anybody know a way to pass an array of values with the MS driver. This way we don't have to rewrite the entlib 2.0 again wo the driver is used instead of the ms oracle driver, just like we did with the 1.1 version.

Another approach is writing a sort of wrapper around the Data block which inherits of the classes in the Data block an add some custom methods that will use the driver. This way i create a mixture but I don't if it is maintanable...

Can anybody give me some advice or help?

Thanks in advance!

Jan 15, 2007 at 2:32 PM
I would just extend the DAAB by creating a custom Database Class for ODP.NET.

It wouldn't be as difficult as you think, because you could probably use all of the current OracleDatabase Class that is already included in the DAAB. You could just add new overload(s) for passing in an array or any other changes you desire.

The EntLib Team just created a new database class specific for SQL Server 2005 Compact Edition, called SqlCeDatabase. You could use it as a guide. It sits in its own assembly just as your database class would.




David Hayden
Microsoft MVP C#
Jan 17, 2007 at 11:19 AM
Ok, thanks.

While waiting for a response i did what you suggested and it actually wasn't that difficult as I thought it would be :-).

Thx for the reply.


Feb 22, 2007 at 10:36 AM
Yves, I´m working in a personal project and I want to use DAAB 2.0 with ODP.NET. Could you provide me a sample of your ODP.NET DAAB implementation? It would be very helpful for me.


Jun 23, 2007 at 3:22 AM
Edited Jun 23, 2007 at 3:22 AM
Hi Guys!

Can you send a copy to me too?



Jun 30, 2007 at 12:38 AM
yves, can I get a copy as well?

Thanks !

yvesroyer wrote:
Ok, thanks.

While waiting for a response i did what you suggested and it actually wasn't that difficult as I thought it would be :-).

Thx for the reply.



Apr 17, 2008 at 12:29 PM
Hi David,

we wanted to use ODP.NET 11 with Enterprise Lib 3.0 . Can you please me your extension code at


Jun 13, 2008 at 1:24 AM

I am having exactlyn the same problem, if available would you be able to send me a copy of the implementation also.

Many Thanks

Oct 13, 2008 at 9:53 PM
Hi Yves,

I tried doing what David said and am interested in comparing my code with what you came up w/.  Would you want to swap code files?

Dec 10, 2008 at 9:08 PM
Hi all.

I would be interested in seeing a copy of someone's current implementation agains EL 4.x.    Could anyone supply a sample?  

We have an existing ODP.NET extension project from EL2.0 days/Oracle 9i.

Dec 11, 2008 at 8:35 AM
Hi, check out this contribution.  It wasn't built against 4.1 version of EntLib but thought it might help anyway.

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.