How to get the EXACT amount of rows Copied on a bulkcopy insert ?

[rferraton]

It is quite surprising not to see a RowsCopied Property returned by the bulkcopy method and giving the total rows copied. It is the case on many other database .net clients (mssql, mysql, pgsql...)

Counting the target table is totally false in the case table already having data before bulk insert or long if the table is big and was empty. So the example given is a little bit disappointing : https://github.com/oracle/dotnet-db-samples/blob/95b7321c6510d07884a5160f2c81b4fc79977ec2/samples/bulk-copy/oracle-bulk-copy.cs#L54

Currently i use the OracleRowsCopied EventHandler BUT it seams that it is triggered only after a number of rows bulked. This number is determined by the NotifyAfter Property.

obc.NotifyAfter = 10000;
long crows = 0;
obc.OracleRowsCopied += (sender, args) =>
{
   crows = args.RowsCopied;                     
};

The problem is that the remaining rows between the last OracleRowsCopied Event and the final rows are not "detected".

Is there another method (and if possible avoiding to set NotifyAfter=1)

[alexkeh]

@rferraton Once your BulkCopy operation completes, can't you retrieve the RowsCopied and perform a mod 1000 operation to find out the "remainder" of rows copied?

[rferraton]

@rferraton Once your BulkCopy operation completes, can't you retrieve the RowsCopied and perform a mod 1000 operation to find out the "remainder" of rows copied?

No, i don't get how you can find the exact using this technic.

I think personnaly there is a lack\bug in the trigger that don't raise at the end of the load

[alexkeh]

The SqlBulkCopy NotifyAfter documentation says: "This property is designed for user interface components that illustrate the progress of a bulk copy operation. It indicates the number of rows to be processed before generating a notification event."

By design, NotifyAfter does not trigger at the end of the load operation necessarily based on MS's definition. ODP.NET follows that same behavior.

Here's what I mean about using the mod operation.

obc.NotifyAfter = 10000;
long crows = 0;
obc.OracleRowsCopied += (sender, args) =>
{
   crows = args.RowsCopied;                     
};

// Use mod operator to record the remaining rows that were bulk copied after the last NotifyAfter event.
short extraRows = crows % 10000;
// Trigger event to output extraRows value.

[rferraton]

No you solution does not work. (tested)

Let's imagine you have 5 rows to load and NotifyAfter is set to 1000

obc.OracleRowsCopied += (sender, args) =>
{
   crows = args.RowsCopied;                     
};

will never by trigered ==> crows will remain equal to 0 extraRows = 0

Conclusion : it is currently not possible to get the exact number of rows copied using bulkcopy

[alexkeh]

It's possible you're hitting bug 34248388. This bug reset the RowsCopied value after every NotifyAfter. Which ODP.NET version and type are you using?

This bug was fixed in ODP.NET 21.12. The fix is also available in the latest ODP.NET 19c version as well. Try upgrading to the latest ODP.NET version if you're not already using it. If this is a new bug, can you publish a full test case and indicate the versions of ODP.NET and DB you are using?

[rferraton]

i used the 3.21.130 and now the 23.2.2-dev : same problem

[alexkeh]

Ok. I see now. I was able to reproduce the buggy behavior. I filed bug 36418346 to track this issue.

[alexkeh]

After reviewing the APIs with the dev team, we've concluded this issue is not a bug. ODP.NET's definition for NotifyAfter is similar to SqlClient's in that the property is intended for UI components to measure progress.

This property can be retrieved in user interface components to display the progress of a bulk copy operation.

For ODP.NET RowsCopied, the description states

This event is raised when the number of rows specified by the NotifyAfter property has been processed. It does not imply that the rows have been sent to the database or committed.

This is similar to SqlClient RowsCopied property, which states

This value is incremented during the SqlRowsCopied event and does not imply that this number of rows has been sent to the server or committed.

Based on the doc, the ODP.NET APIs are behaving as documented and matches ADO.NET's intent.

With that said, we do see value for this feature request. We are considering adding a new API that triggers an event after all rows are copied. If that API is used, we could populate RowsCopied with the total number of rows copied.

Thus, we're making this bug an ER instead.

[rferraton]

Thanks to push to DevTeam 👍

Agree : OracleRowsCopied Event have the behavior describe in the documentation. Agree : at the time of writing, there is no way to know the exact number of row copied by the bulkCopy. This is not a bug but a lack ==> ER could be a good idea

Personnaly i prefer a gettable property instead of an event. I found "property way" more readable and safer with multiple-threads.

[alexkeh]

Looks like we'll get this change in sooner than expected. It will be part of ODP.NET 23.5, as well as 21.15 and 19.24.

[alexkeh]

ODP.NET 23.5 is now available.