Is OraOLEDB provider in .NET unreliable on CLOB fi

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");
        }

Answer 1

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.

Answer 2

Fantastic !

Had similar problem where a recordset containing two records ( tiff files ) was displaying the first record successfully but giving the error re: argument type on the second record.

Determined that only one dll in the patch for the OLE fix is changed and just replaced this single dll ( OraOLEDBrst11.dll within the oracle client bin directory ).

Works fine now.

Answer 3

That's a strange behavior. The only reference I found in forums to such an issue is this old one, back to Oracle 8!

Debugging our Open Source DB classes in un-managed Delphi code, I found out the same issue! So it sounds not like a DotNet or ADO error, but an issue on the OraOleDB provider side... :(

The BLOBs retrieved from our direct OCI classes just contain all existing BLOB content, whereas the OleDB version does sometimes return NULL, not for all rows!

Sounds definitively like an issue with the provider. I would recommend using direct OCI access (Oracle Native access on DotNet), and do not rely on Oracle's OleDB provider...

I tested this with latest Oracle 11g Client and Server, with 32 bit client under Win64.