ODP.NET bulk copy parallel loads feature request

[rferraton]

When i use an APPEND_VALUES hint in my Insert command and execute it using ExecuteNonQuery i receive an error message :

Message : ORA-38910: BATCH ERROR mode is not supported for this operation
StackTrace :    at OracleInternal.ServiceObjects.OracleFailoverMgrImpl.OnError(OracleConnection connection, CallHistoryRecord chr, Object mi, Exception ex, Boolean bTopLevelCall, Boolean& bCanRecordNewCall)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()

is it possible to set off the the error verifications somewhere and if not, would it be possible to push the demand to dev team ?

[alexkeh]

You can not turn off this error. However, you can set up a try-catch block to hide it from the end user. The catch block gets triggered and just continues app operations afterwards without the user seeing the error.

[rferraton]

You can not turn off this error. However, you can set up a try-catch block to hide it from the end user. The catch block gets triggered and just continues app operations afterwards without the user seeing the error.

Well ... i do want insert data into the target, your method just avoid to throw the error but the insert with /*+ APPEND_VALUES */ hint does not work. Your method just hide the failed inserts silently

My final objective is to make direct path insert (in parallel) to a single table.

• OracleBulkCopy lock the target, so write parallelism is not currently possible with oracle with this (it is possible with postgresql, sql server and other databases....) I still don't understand why OracleBulkCopy cannot be parallel while SQL*LOADER can.
• Bind Array and conventionnal path is the only way i know with odp.net to load one oracle table in parallel. But it is cpu intensive for Oracle and does not scale well.
• I tried with /*+ SYS_DL_CURSOR */ hint, but it seam the hint is ignored (in 21 at least) and the inserts use conventional path.
• I had hope with APPEND_VALUES hint + bind arrays but the ORA-38910: BATCH ERROR mode stopped me.

[alexkeh]

Sorry, I misunderstood your original question. I thought you wanted to suppress the error only.

ODP.NET hasn't implemented bulk copy parallel loads. If there are enough votes for this feature, we will prioritize it for one of our quarterly updates,

[alexkeh]

How do SqlClient and Npgsql implement parallel bulk copy? The most straightforward implementation for ODP.NET may be emulating that method. However, it doesn't seem like there is a BulkCopy API they use to enable parallelism. Instead, the process appears to be manual.

Do you have an example of the bulk copy code you use with SQL Server or PostgreSQL that shows this parallelism?

[rferraton]

for Ngpgsql

you have method on the connection to start/stop a writer var writer = npgsqltargetConnection.BeginTextImport(pgcopycommand) Then you can iterate over a dbdatareder , getvalue and use the writer :

var writer = npgsqltargetConnection.BeginTextImport(pgcopycommand);
while (sdr.Read())
{
				for (int i = 0; i < sdr.FieldCount; i++)
				{		if (i > 0)
								writer.Write(CsvDelimiter);
					object value = sdr.GetValue(i);

					if (value != null)
						{
							writer.Write(CsvQuote);
							writer.Write(value.ToString());
							writer.Write(CsvQuote);
						}
				}

           writer.WriteLine();
}
writer.Flush();
writer.Close();
writer.Dispose();

This part can be parallelized in a parallel for loop and are truly parrallel (several postgres process COPY from stdin to the same target table without locks)

For SQL Server :

sbc = new SqlBulkCopy(sqlTargetConnection as SqlConnection, options, null);
sbc.DestinationTableName = $"{targetschema}.{targettable}";
sbc.BulkCopyTimeout = bulktimeout;
sbc.BatchSize = bulkbatchsize;
sbc.EnableStreaming = true;
using (DbDataReader sdr = sqr.ExecuteReader())
{
	if (mapmethod == "Name")
	{
		List<string> targetColumns = GetTargetTableColumns("msbulk", targetschema, targettable, sqlTargetConnection);
		MapColumnsByName(targetschema, targettable, sbc, targetColumns, sdr);
	}

	ccols = sdr.FieldCount;

	sbc.WriteToServer(sdr); // Will read all the sdr and push to sbc. Synchronous is faster.
	crows += sbc.RowsCopied64;
}

This part can be parallelized in a parallel for loop and are truly parrallel (several sql server session process bulk insert from the dbdatareader to the same target table without locks)

For Oracle

It is quite similar to SQL Server, but it failed when running in parallel. I think it would be beneficial to implement a parallel load feature in ODP.NET to achieve SQL*Loader parallel load capability. The faster you can integrate data, the easier it will be for people to move data to Oracle instead of from Oracle.

[alexkeh]

I've created ER 38517030 to track this enhancement request.

[rferraton]

If you need i will be able to make some tests when the feature will be in preview, just ping and ask me