Oracle ODP.NET Versus Built-In .NET 1.1 Oracle Support

  • Thread starter Thread starter Robert Reineri
  • Start date Start date
R

Robert Reineri

Hello All,

Does anyone know what benefits, if any, there are to using Oracle ODP.NET
from Oracle vs. .NET's built-in System.Data.OracleClient stuff? We are doing
pretty basic queries and stored procs, and unless there is some compelling
reason, I would rather not load yet another piece of software on our
systems.

I have scoured the WWW and can't find anything at all on this topic. Wonder
if any of you have opinions or real-world experience in this area.

Please reply via newsgroup.

Thanks in advance,

Robert
 
Robert Reineri said:
Hello All,

Does anyone know what benefits, if any, there are to using Oracle ODP.NET
from Oracle vs. .NET's built-in System.Data.OracleClient stuff?
Click to expand...

The benifits are better support and access to cool Oracle-only features like
array binding, pl/sql tables, better LOB functionality, Oracle XML, better
ref cursor support, etc. But for basic stuff it works about the same.
We are doing pretty basic queries and stored procs, and unless there is some compelling
reason, I would rather not load yet another piece of software on our
systems.
Click to expand...

It's not really another piece of software. You have to load the Oracle
Client anyway, and the ODP.NET install comes bundled with the Oracle Client
install. So you have to load a total of one extra piece of software with
either provider.

One advantage that the MS provider has over ODP.NET is that the MS provider
appears to work with the Oracle 10g Instant Client, which can be XCopied
into your bin folder or anywhere on your path or bundled with your custom
install for easy deployment.

http://otn.oracle.com/tech/oci/instantclient/instantclient.html

MS says that the OracleClient "Supports Oracle client software version 8.1.7
and later." and it actually does work, but you'll want to test pretty
extensively since 10g is pretty new.

ODP.NET does not work with the InstantClient, only the full client.

David
 
Sahil Malik said:
I could not find a way to do SavePoints in OracleClient. Though ODP.NET did
let me do so.
Click to expand...

OracleClient's OracleTransaction doesn't have a SaveTransaction method, but
it can't stop you from issuing a SAVEPOINT. This should work:

public void SavePoint(OracleTransaction tran, string Name)
{
new OracleCommand("SAVEPOINT " + Name,tran.Connection,
tran).ExecuteNonQuery();
}
public void RollbackToSavepoint(OracleTransaction tran, string Name)
{
new OracleCommand("ROLLBACK TO SAVEPOINT " + Name,tran.Connection,
tran).ExecuteNonQuery();
}

Also there's much less need to set savepoints in Oracle since all PL/SQL
programs are atomic. So if you wrap a number of statements in a PL/SQL
block they will either all succeed or all fail. In fact each PL/SQL block
is implictly wrapped in a SAVEPOINT/ROLLBACK pair.

David
 
Great, I had posted a question on this group a few days back asking for the
very same question ..

So OracleTransaction won't let me do it directly .. but hey afterall I can
execute any command I wish .. I get it :) thanks.

- Sahil Malik
Independent Consultant
You can reach me thru my blog - http://dotnetjunkies.com/WebLog/sahilmalik/
 
Thank you David. Sounds like you've worked with it quite a bit. I appreciate
the input.

One thing I could not seem to get ODP.NET to do was to work with named
parameters. When I called the stored proc, it always insisted on trying to
use them in a "positional" way. I tried the same code with the built-in
provider in .NET, and it worked perfectly.

That is, thusly:

PROCEDURE X (parm1 IN VARCHAR2 default null, parm2 IN VARCHAR2 default
null)
BEGIN
do something
END

and the C# code:

// Create some parameters.
OracleParameter parm1 = new OracleParameter(":parm1", OracleDbType.Varchar2,
ParameterDirection.Input);
OracleParameter parm2 = new OracleParameter(":parm2", OracleDbType.Varchar2,
ParameterDirection.Input);

// Add them to the OracleCommand .. note order of adding to collection.
command.Parameters.Add(parm2);
command.Parameters.Add(parm1);

// Execute
command.ExecuteNonQuery();

running this code would always put parm2 in the 1st position when arriving
at the stored proc, and parm1 in position 2. Of course, this is a contrived
example - the actual stored proc had about 10 parameters, and I only wanted
to use 2 or 3 of them at a time.

I converted the above over to System.Data.OracleClient and it worked fine.
The parameters would be appear in their proper places in the stored
proc.

If you have any ideas on this (I'm sure I must be doing something wrong) I
would greatly appreciate a pointer!

Thanks

Robert
 
Robert Reineri said:
Thank you David. Sounds like you've worked with it quite a bit. I appreciate
the input.

One thing I could not seem to get ODP.NET to do was to work with named
parameters. When I called the stored proc, it always insisted on trying to
use them in a "positional" way. I tried the same code with the built-in
provider in .NET, and it worked perfectly.

That is, thusly:

PROCEDURE X (parm1 IN VARCHAR2 default null, parm2 IN VARCHAR2 default
null)
BEGIN
do something
END

and the C# code:

// Create some parameters.
OracleParameter parm1 = new OracleParameter(":parm1", OracleDbType.Varchar2,
ParameterDirection.Input);
OracleParameter parm2 = new OracleParameter(":parm2", OracleDbType.Varchar2,
ParameterDirection.Input);
Click to expand...

command.BindByName = true;

IN ODP.NET parameters are bound by position by default. To change that set
the OracleCommand's BindByName property.

David
 
Thank you so much David. We have been goofing around with this for days now!

Should have come here earlier...thanks again.

Robert
 
Hi David.

We just started the development of an ASP.NET application using ODP.NET. Do
you know of any negative impact on changing this BindByName property? Should
we keep our code using positional parameters when calling Oracle procedures?

TIA,
Erik Cruz
 
Erik Cruz said:
Hi David.

We just started the development of an ASP.NET application using ODP.NET. Do
you know of any negative impact on changing this BindByName property? Should
we keep our code using positional parameters when calling Oracle procedures?
Click to expand...

There is no negative impact. Use whichever setting you find more
conventient.

David
 
Back
Top