How to read a CLOB column in Oracle using OleDb?

Question

I have created a table on an Oracle 10g database with this structure :

create table myTable
(
id       number(32,0)      primary key,
myData   clob
)

I can insert rows in the table without any problem, but when I try to read data from the table using OleDb connection, I get an exception.

Here is the code I use :

using (OleDbConnection dbConnection = new OleDbConnection("ConnectionString"))
{
    dbConnection.Open();

    OleDbCommand dbCommand = dbConnection.CreateCommand();

    dbCommand.CommandText = "SELECT * FROM myTable WHERE id=?";
    dbCommand.Parameters.AddWithValue("ID", id);

    OleDbDataReader dbReader = dbCommand.ExecuteReader();
}

The exception details seems to point on an unsupported data type :

System.Data.OleDb.OleDbException: 
Unspecified error Oracle error occurred, but error message could not be retrieved from Oracle. 
Data type is not supported.

Does anyone know how I can read this data using the OleDb connection ?

PS : The driver used in this case is the Microsoft one.

Cystoid

Answer 1

I'm not sure it is possible to use the CLOB type with the Microsoft OLEDB driver.

Do you have to use the Microsoft one? You would be much better to use the Oracle Provider for OLE DB or better yet just use Oracle Data Provider for .NET (ODP.NET).

Dixon

I've been able to read the data using Oracle ODP.NET driver, but the existing application uses OleDb for every connection. Changing the default driver would need some code migration and installing ODP.NET on clients. That's why I'd prefer sticking to OleDb driver. – Cystoid

I don't know if this Microsft KB article (support.microsoft.com/kb/244661) still applies, but it says 'Oracle 8.x-specific data types, such as CLOB, BLOB, BFILE, NCHAR, NCLOB, and NVARCHAR2, are not supported'. If it does, I think you're out of luck :( – Dixon

Also, take a look at this if you haven't already: groups.google.co.uk/group/borland.public.delphi.database.ado/… – Dixon

Well, it seems that I have to switch my driver then, I'll try to see if I can have both in the same application. – Cystoid