C# DbDataReader iterating OracleClob Bug

C# DbDataReader iterating OracleClob Bug, When you use C# to connect to the Oracle database, you may encounter some unexpected errors. I also encountered similar problems in my work. I happened to see that some friends encountered them on stackoverflow. The following content is from this post.

DbDataReader iterating OracleClob Bug

I wrote an app running on multiple client machines connecting to a remote oracle database (master) to synchronize theirs local open source database (slave). This works fine so far. But sometimes a local table needs to be fully initialized (dropped and afterwards all rows of master database inserted). If the master table is big enough (ColumnCount or DataType/DataSize and a certain RowSize) the app sometimes runs into an OutOfMemoryException. The app is running on windows machines with .NET 4.0. Version of ODP.NET is 4.122.18.3. Oracle database is 12c (12.1.0.2.0).

I don’t want to show the data to any user (the app is running in the background), else i could do some paging or filtering. Since not all tables contains keys or are able to be sorted automatically its pretty hard to fetch the table in parts. The initializing of the local table should be done in one transaction without multiple partial commits. I can brake the problem down to a simple code sample showing the managed memory allocation which I didn’t expect. At this point I’m not sure how to explain or solve the problem.

using (var connection = new OracleConnection(CONNECTION_STRING))
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = STATEMENT;

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                //reader[2].ToString();
                //reader.GetString(2);
                //reader.GetValue(2);
            }
        }
    }
}

DbDataReader iterating OracleClob Bug

When uncommenting any of the three reader.* calls the memory of the requested column data seems to be pinned internally by ODP.NET (OracleInternal.Network.OraBuf) for each record. For requesting a few thousand records this doesn’t seem to be a problem. But when fetching 100k+ records the memory allocation gets to hundreds of MB, which leads to an OutOfMemoryException. The more data the specified column has the faster the OOM happens (mostly NVARCHAR2). Calling additionally GC.Collect() manually doesn’t do anything. The GC.Collect()’s shown in the image are done internally (no calls by myself).

Since I don’t store the read data at any place I would have expected the data is not cached while iterating the DbDataReader. Can u help me understand what is happening here and how to avoid it?

This seems to be a known bug (Bug 21975120) when reading clob column values using ExecuteReader() method with the managed Driver 12.1.0.2. The Workaround is to use the OracleDataReader specific methods (for example oracleDataReader.GetOracleValue(i)). The OracleClob value can explicitly be closed to free the Memory allocation.

var item = oracleDataReader.GetOracleValue(columnIndex);

if (item is OracleClob clob)
{
    if (clob != null)
    {
        // use clob.Value ...

        clob.Close();
    }
}

DbDataReader iterating OracleClob Bug