Sql-server – Why can’t I read Oracles CLOB columns via SQL-SERVER 2008 linked server

oracleoracle-11gsql-server-2008

I want to access data in an Oracle 11g database from SQL-Server 2008

I set up a linked server and when I execute

select * from [Link_server_name]..Oracle_schema.Oracle_table

and Oracle_table contains Number and varchar2 columns, all works as excepted.

But when the Oracle_table contains a CLOB column, I get the following error:

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Unspecified error' zurückgeben.

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Oracle error occurred, but error message could not be retrieved from Oracle.' zurückgeben.

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Data type is not supported.' zurückgeben.

Msg 7306, Level 16, State 2, Line 1

Die '"MCCAPP"."DOGGRUPPEN"'-Tabelle vom OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' kann nicht geöffnet werden.

Using OPENQUERY

SELECT * FROM OPENQUERY([L_V407SR8T], 'Select CLOB_COLUMN from Oracle_table'  )

I get

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Oracle error occurred, but error message could not be retrieved from Oracle.' zurückgeben.
Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Data type is not supported.' zurückgeben.

Please excuse German error messages.

My question: Is there any way to read CLOB columns via linked Servers?

EDIT:

  • SQL Server Import and Export Wizard
    seems to depend on OLE DB too and
    sucks on the same tables
  • I'm asking myself, if the problem
    depends on the character sets uses,
    but I can't change them
  • As practical work around, I use some
    PowerShell scripts to get at the
    data, but

Best Answer

Download and install an Oracle 11 database client on your SQL Server 2008 machine.

Set up a linked server using an Oracle OLE DB provider (OraOLEDB.Oracle).

Make sure "Allow InProcess" is enabled in Provider options.

The Oracle 11 OLE DB client supports CLOBs.