Oracle MERGE statement not 'sticking'

[davidesharpe]

Hi,

I'm using a Oracle MERGE statement in dotConnect for Oracle, I know that the MERGE itself is fine as runs in P-Sql and SQL Developer, yet using the dotConnect and ExecuteNoQuery the query is apparently successful but the table has had no updates or inserts taken place.

Any Ideas ?

[MariiaI]

You are working in the Direct mode, aren't you? We have reproduced the issue with performing merge statements when working in Direct mode without explicitly commiting the transaction. We will investigate this issue in more details and inform you about the results as soon as possible.
As a workaround, you can try using:
1) the OCI mode;
or
2) this code:

Code: Select all

OracleConnection conn = new OracleConnection("User Id=user;Password=pass;Server=dboracle;SID=sid;Direct=true");
conn.Open();
OracleTransaction t = conn.BeginTransaction();
OracleCommand comm = new OracleCommand(@"MERGE INTO ...");// place here your merge statement
comm.Transaction = t;
comm.Connection = conn;
comm.ExecuteNonQuery();
t.Commit();

Please tell us if this helps.

[MariiaI]

You are working in the Direct mode, aren't you? We have reproduced the issue with performing merge statements when working in Direct mode without explicitly commiting the transaction. We will investigate this issue in more details and inform you about the results as soon as possible.

The bug with committing automatically the Merge statement executed via OracleCommand in the Direct mode is fixed in the new (8.4.274) build of dotConnect for Oracle. It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).

For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=30639 .