Is OraOLEDB provider in .NET unreliable on CLOB fields?

Question

I have a query that seems to be returning the wrong data from the OleDbDataReader. I've included the actual code below (with user id and pass changed). I'm running on Oracle Express 10g.

If I run the SQL below in the Oracle Web Admin Utility, from Toad, or if I change the the code below to be OracleConnection, all 4 records that are returned have values in the "answer_text" column.

From OleDbDataReader, however, this code throws an exception on the second record, where it is returning null for "answer_text" instead of the expected value: "This is the second open ended comment. Things are really getting crazy now."

The field in question is defined as CLOB in the database.

So my question is, is the OraOLEDB provider just plain unreliable? This is really spotty behavior. 3 of the 4 records return data correctly. Simply changing out OracleConnection with OleDbConnection and removing "Provider" from the connection string seems to work fine.

I don't think my client wants to do this, though, for distribution/support reasons. Oracle is only 1 of 4 database vendors we support, and this code works fine for the rest.

Is this a known bug? Is there some arcane setting I'm missing? I don't have very much experience with Oracle, and I couldn't find anything from googling this issue. Any advice would be appreciated.

        var connection = new OleDbConnection("Provider=OraOLEDB.Oracle;Data Source=localhost;User Id=x;Password=x;");
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = "select response_id, item_id, subitem_id, answer_id, answer_text, other_text, column_answer_id from sur_response_answer where item_id in (180, 181)";
        var reader = command.ExecuteReader();
        while (reader.Read())
        {
            if (reader.IsDBNull(reader.GetOrdinal("answer_text")))
                throw new Exception("This should not be happening");
        }

The drivers themselves don't throw any kind of error. They just return null when they should be returning data. So in the example above, you would get an exception with the message: "This should not be happening". — Mike Van Til

Unknown Unknown · Accepted Answer · 2011-07-12T07:54:11

Oracle Support has bug 9866728, but that applies to OLEDB provider 11.2.0.1. It has been fixed in the patches listed in document 1272856.1. To access those documents and the patches you need an Oracle Support account.

About Oracle Express 10g, it is known to be an unsupported release (no patch was ever released) with some known bugs - I would not use it for a production application. Joining the Oracle Partner Network will allow you to use development/test license to develop and test against the actual releases, not an outdated (although free...) one.

Is OraOLEDB provider in .NET unreliable on CLOB fields?

3 Answers