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.