OracleLoader.NextRow() Goes Silent After ~36000 Rows Processed.

[NMH]

I'm loading some data rows from a DataTable object using a foreach loop like the example below.

Each column is extracted from the DataRow and set by index using OracleLoader.SetString() OR OracleLoader.SetNull(). This exact code is shared with processes that load smaller amounts of data with NO ISSUE. After around 35k-36k rows the process pauses at the line OracleLoader.NextRow(). It sits here forever until it is forcefully stopped.

Oracle Monitor shows that the DB (11gR2) is waiting for the client. No blocked or locked tables. No open commits. The process just sits there with no activity whatsoever. I can repeat this example EVERY TIME but the crazy part is that the exact row in which it stops is slightly different every time. The data I'm inserting is ordered by key, so it does not make sense that the problem row is related to column length, type, or constraint. Sometimes ahead, sometimes behind. This led me to believe that there may be an inefficient buffer setting. I've tried high and low numbers to no avail (or I get out of memory exception). All results the same. Please help.

Code: Select all

foreach (DataRow row in inputTable.Rows)
{
	for (int columnIndex = 0; columnIndex < row.ItemArray.Count(); columnIndex++)
	{
		if (row.Table.Columns[columnIndex].DataType == Type.GetType("System.DBNull"))
		{
			loader.SetNull(columnIndex);
		}
		else
		{
			string value = row.ItemArray[columnIndex] as string ?? String.Empty;
			loader.SetString(columnIndex, value);
		}
	}
	
	loader.NextRow();
}

EDIT:
FYI I am using version 7.9.333 23-Sep-13
I cannot find any relevant release notes for my particular issue. Unless i'm missing something?
http://www.devart.com/dotconnect/oracle ... story.html

[Pinturiccio]

We could not reproduce the issue with the provided code. In our environment your code works without errors. Please send us a complete small test project with a DDL scipt for reproducing the issue.

[NMH]

After analyzing the issue we discovered that there were constraint violations with the data that we were attempting to input.

The troublesome issue here is that we did not get an Array DML exception. Just the silent behavior that I described above. We were only able to uncover this through extensive logging and debugging of the data input.

We also discovered that aggressive management (tightly scoped "using(){}" statements) of both the loader and connection objects on a regular batch seemed to improve performance/error response.

[Pinturiccio]

The OracleLoader class uses Oracle direct-path functionality. When performing direct-path INSERT, constraints are ignored. After completing direct-path INSERT constraints come info effect again. All this is performed by Oracle; our provider only loads data.

To help us reproduce and fix your issue, please send us the following:
1. The DDL script of the table;
2. A small test project which reproduces the issue;
3. Exact version of Oracle database and Oracle client.