Data Provider for Oracle: MS version vs. Oracle?

[Marina Levit [MVP]]

Anyone have experience with the provider from Microsoft vs. the one from
Oracle? I haven't looked very much at either.
One would assume that the one written by Oracle itself should perform
better, since they should be able to optimize it?

Any sort of feedback or experience with either of these?

 

[Guest]

I used MSDAORA with Oracle and it seemed to work the best for my ASP.Net 1.1.

 

[Cor Ligthert [MVP]]

Marina,

In my opinion a typical question for Frans Bouma, you know the one I have
always those big fights with in these newsgroups.

I did not see him a while here so mail me, than I sent you his email adres.
You have to translate my emailadress first of course.

Cor

 

[Guest]

My application can use both, our data abstraction layer permits changing the
provider from an external xml configuration file. In fact to any provider
targeting that database that was decent written. Then I was able to do quite
easy tests of speed and so.
So yes, the Oracle is faster ... Take care also that they make different
casts inside the readers get methods, this if you plan to try one and then
try the other.
With the Oracle, redistribution is the strangest part, the Microsoft handled
that more elegant.

 

[Frans Bouma [C# MVP]]

Anyone have experience with the provider from Microsoft vs. the one
from Oracle? I haven't looked very much at either.
One would assume that the one written by Oracle itself should perform
better, since they should be able to optimize it?

MS' version doesn't support the XML type, it always returns any
NUMBER(x,y) as decimal and doesn't have some of the fancy features
ODP.NET supports. There are also minor issues with varchar fields: you
can't save a UNICODE encoded string in a varchar field, because when
you set the parameter's OracleType to VarChar, it will result into
using DbType.AnsiString under the hood, allowing only non-unicoded
strings. This is of course to support 8i, but when you're using 9i or
higher this sucks bigtime if you need to store unicoded strings.

ODP.NET and MS' provider are on par with performance, as both rely on
the Oracle CLI (client interface). You have to install the client no
matter what, so with MS' provider you've to install the client
separately, with ODP.NET you get the client installed.

I'd pick ODP.NET without any hesitation. Not only does it support more
features but you can say what you want about Oracle, they DO release
bugfixes from time to time, while Microsoft is very slow to release
bugfixes (service packs for .net framework).

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Marina Levit [MVP]]

Thanks to everyone who responded.

 

[Guest]

I do stand by what I said (but if I’m wrong I leaned something).
Of what version of Oracle are we talking?
I do suppose that are we talking about .Net 2.0 providers.

 

[Paul Clement]

¤ Anyone have experience with the provider from Microsoft vs. the one from
¤ Oracle? I haven't looked very much at either.
¤ One would assume that the one written by Oracle itself should perform
¤ better, since they should be able to optimize it?
¤
¤ Any sort of feedback or experience with either of these?
¤

Oracle's provider's generally support more native features, but I've generally found then to be a
bit more buggy. Also, keep in mind that ODP.NET requires the 9i client version or higher.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Cor Ligthert [MVP]]

Frans,

while Microsoft is very slow to release
bugfixes (service packs for .net framework).

I have seen they are changing their policy, so I would not write this
anymore or at least write "was", they have showed they listen in this to
their customers..

Cor

 

[Frans Bouma [C# MVP]]

I do stand by what I said (but if I’m wrong I leaned something).

that's great but my experiences are different. Also, it's not easy to
swap providers, because of the NUMBER(x, y) -> .NET type mappings which
differ alot between ODP.NET and MS' oracle provider.

Of what version of Oracle are we talking?

that doesnt' matter.

I do suppose that are we talking about .Net 2.0 providers.

that also doesn't matter in this case.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Guest]

Ok so let’s call it tie , because we had such different experiences, and
both are so convinced by the validity of the results.

About swapping the database abstraction layer we use make it very simple,
just modify one value in the configuration file and you are done. Even can
try to use the native one and if is not available use the Microsoft one. And
of course that takes care of data type mapping, the data reader will give the
“good valuesâ€.

The most comprehensive test on this issue we have done with Oracle 9i and
with the 1.1 versions of the providers. We have tried with the “other
versions†but not so thoroughly, the difference seemed to be there… And the
database abstraction layer is not to blame, or who knows, maybe a different
programming approach, specific for each of the provider will give better
performance.

 

[jonathan.m.bruce]

You should probably broaden your search beyond these two if you are
looking for providers. This page here has some useful additional
references. And you assumption doe snot always hold water...

http://en.wikipedia.org/wiki/ADO.NET#References

 

[Frans Bouma [C# MVP]]

Ok so let’s call it tie , because we had such different
experiences, and both are so convinced by the validity of the results.

About swapping the database abstraction layer we use make it very
simple, just modify one value in the configuration file and you are
done. Even can try to use the native one and if is not available use
the Microsoft one. And of course that takes care of data type
mapping, the data reader will give the “good valuesâ€.

Erm... let's say I have this code:
int customerID = myCustomer.ID;

and myCustomer is a Customer entity filled from Oracle using ODP.NET.
Now, you say you just flip a switch in some config file.

Erm... you can do that, but that doesn't mean my int customerID code
will all of a sudden become decimal customerID = ...

FB

The most comprehensive test on this issue we have done with Oracle 9i
and with the 1.1 versions of the providers. We have tried with the
“other versions†but not so thoroughly, the difference seemed to
be there… And the database abstraction layer is not to blame, or
who knows, maybe a different programming approach, specific for each
of the provider will give better performance.

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Guest]

You are not giving up I see So let’s slip a bit into off topic.
Probably is an interesting topic for you considering what your company does,
in fact we are talking about a solution to the same problem but a completely
different approach.

OK so this is how it works, you have a reader or any data object.
You want a data value if it works int i = reader.GetInt32(8) for Microsoft
provider will work also for Oracle (even if the native provider returns a
decimal value or anything). In the data object called is taken care of
uniform type conversion.
As programming model you use the database abstraction layer data objects as
are used in the simplest MSDN sample, you have them reader table etc. As
result, you get an application 100% agnostic of the database.
To say more the flip of the switch just say what provider is preferred, the
first one found will be used.
Again this DAL works for SQLServer, Oracle, PostgreSQL, all DB2 (AS 400 and
Universal), and other database are in preparation, I’m told.
And not the least for all the databases supported you get the catalog info,
tables, columns, types, triggers so on.

 

[Frans Bouma [C# MVP]]

You are not giving up I see So let’s slip a bit into off topic.
Probably is an interesting topic for you considering what your
company does, in fact we are talking about a solution to the same
problem but a completely different approach.

OK so this is how it works, you have a reader or any data object.
You want a data value if it works int i = reader.GetInt32(8) for
Microsoft provider will work also for Oracle (even if the native
provider returns a decimal value or anything). In the data object
called is taken care of uniform type conversion.

ah, but you need a type conversion layer somewhere. Sure, I have that
too (to /from any .NET type if you want, it's pluggable), though normal
usage with just the ado.net provider, you won't get this working.
ODP.NET OracleDataReader.GetInt32 won't give you an int if the
precision is >= 10. With MS' provider this is unclear (calls into
native OCI methods)

As programming model you use the database abstraction layer data
objects as are used in the simplest MSDN sample, you have them reader
table etc. As result, you get an application 100% agnostic of the
database. To say more the flip of the switch just say what provider
is preferred, the first one found will be used.
Again this DAL works for SQLServer, Oracle, PostgreSQL, all DB2 (AS
400 and Universal), and other database are in preparation, I’m
told. And not the least for all the databases supported you get the
catalog info, tables, columns, types, triggers so on.

Sure, with a conversion layer, anything is possible, I know that, but
I was talking about people using the ado.net provider and default .NET
constructs, i.e. not using a 3rd party dal / OR mapper.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Guest]

Marina:

The MS version does not appear to support multiple result sets coming back
from a stored procedure. The Oracle one does.

There also appear to be differences on how date constants are interpreted
when passing a SQL command.

 

[Petar Repac]

article by MS:
Comparing the Microsoft .NET Framework 1.1 Data Provider for Oracle and
the Oracle Data Provider for .NET
http://msdn2.microsoft.com/en-us/library/ms971518.aspx

ODP.NET by my experience has more features.
PL/SQL Associative Arrays are just one example of feature that MS Oracle
provider lacks
http://www.oracle.com/technology/oramag/oracle/07-jan/o17odp.html