Selecting BLOB column from Oracle database to SQL Server database over linked server

1
votes

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.".

2
sql-serveroraclelinked-serverblobs
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

2 Answers

1
votes

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.

0
votes

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.