ODP.NET ExecuteNonQuery Affected Rows Problem

[David F.]

Hi,

I'm using ODP.NET for connecting to Oracle 8i 8.1.7.4 from C# Data
Access Object.
I'm experiencing a problem with ExecuteNonQuery() from OracleCommand
object.
When I execute a Stored Procedure command that produces an INSERT,
UPDATE or DELETE, the returning value is always -1 instead correct
affected rows count.

If I try to execute the same SQL Command directly (I mean, with no
packages or stored procedures), the ExecteNonQuery method returns the
right value (number of affected rows).

The same problem occurs with ArrayBinding support and Packages (or
Stored Procedures).

This behaviour appeared when I changed (or upgraded) ODP.NET Version
from 9.2.0.401 to 9.2.0.406 (and newer versions too).
I tried with Oracle 10g client and ODP.NET 10.1.0.2 and the returning
value remians without changes. In earlier versions (9.2.0.2102 and
9.2.0.401) it worked fine.

Please, anybody can provide me some help?

Thanks a lot!

David

 

[Frans Bouma [C# MVP]]

Hi,

I'm using ODP.NET for connecting to Oracle 8i 8.1.7.4 from C# Data
Access Object.
I'm experiencing a problem with ExecuteNonQuery() from OracleCommand
object.
When I execute a Stored Procedure command that produces an INSERT,
UPDATE or DELETE, the returning value is always -1 instead correct
affected rows count.

If I try to execute the same SQL Command directly (I mean, with no
packages or stored procedures), the ExecteNonQuery method returns the
right value (number of affected rows).

The same problem occurs with ArrayBinding support and Packages (or
Stored Procedures).

This behaviour appeared when I changed (or upgraded) ODP.NET Version
from 9.2.0.401 to 9.2.0.406 (and newer versions too).
I tried with Oracle 10g client and ODP.NET 10.1.0.2 and the returning
value remians without changes. In earlier versions (9.2.0.2102 and
9.2.0.401) it worked fine.

As stated in the help for ExecuteNonQuery for odp.net v10.x:
ExecuteNonQuery returns the number of rows affected, for the following:
If the command is UPDATE, INSERT, or DELETE and the XmlCommandType property
is set to OracleXmlCommandType.None. If the XmlCommandType property is set to
OracleXmlCommandType.Insert, OracleXmlCommandType.Update,
OracleXmlCommandType.Delete.
For all other types of statements, the return value is -1.

It should return the amount of rows affected if the query is normal and not
batched.

Frans.