1

How do I select a blob column from an Oracle database using OPENQUERY on a SQL Server that is using Linked Server to connect to the Oracle database?

When I try using this syntax, I receive an error:

SELECT * FROM OPENQUERY(LINKED_SERVER_NAME , 
'Select blobColumn from TABLE') AS derivedtbl_1

Errors:

OLE DB provider "MSDAORA" for linked server "LINKED_SERVER_NAME" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.". OLE DB provider "MSDAORA" for linked server "LINKED_SERVER_NAME" returned message "Data type is not supported.".

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amanda Kitson
  • 5,477
  • 12
  • 49
  • 73
  • If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Feb 17 '11 at 18:27

2 Answers2

1

Can you change the OLE DB provider you're using to connect to Oracle? The Microsoft OLE DB Provider for Oracle does not support BLOB data types. The Oracle OLE DB Provider does. If you change the connection to Oracle to use the Oracle provider, it should work.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I was trying to figure out how to do that earlier actually, when I stumbled upon the answer I posted above. Good idea though! – Amanda Kitson Feb 17 '11 at 18:12
0

This works:

SELECT * FROM OPENQUERY(LINKED_SERVER_NAME , 
'Select DBMS_LOB.SUBSTR(blobColumn,4000,1) from TABLE') AS derivedtbl_1

This returns only the first 4000 characters of the blob columns, and only works for columns that have text (not binary) data. But it works for me.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amanda Kitson
  • 5,477
  • 12
  • 49
  • 73
  • If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Feb 17 '11 at 18:27