SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,670 questions
How to select BLOB column from Oracle database using Link Server in SQL server 2019 DB ?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(105.60000000000001, 92%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EN%3C/text%3E%3C/svg%3E)
Nawaz
1
Reputation point
I have created a Link from SQL server 2019 to Oracle database 19c. Every thing works fine. But when I am selecting the BLOB columns from Oracle, the results are mixed up and shows wrong data. I have done cast BLOB as varbinary max is well. I do cast or no cast it doesnt matters, results are alwyz showing wrong.
Is there any different way of selecting BLOB columns from Oracle using Link Server ?
Regards
Nawaz
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 37%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EN%3C/text%3E%3C/svg%3E)
Nivee 80 Reputation points2023-09-05T04:26:37.02+00:00 When working with BLOB (Binary Large Object) columns in Oracle through a linked server in SQL Server, you should generally cast the BLOB column as "varbinary(MAX)" or an appropriate binary data type. However, if you are experiencing issues where the results appear mixed up or incorrect.
or try this
SELECT CAST(OracleBlobColumn AS varbinary(MAX)) AS SQLServerBinaryColumn
FROM LinkedServerName.OracleSchemaName.OracleTableName
-
Nawaz 1 Reputation point
2023-09-05T04:34:19.88+00:00 Nivee,
I am using below query
select cast([NEWD] as varbinary(max)) as [NEWD] from openquery ([LSERV],'select NEWD from DAT').
The above query showing wrong results only in case of BLOB from oracle, it works well in case of text columns.
Is there any other way of selecting BLOBS columns ?
-
Olaf Helper 44,296 Reputation points2023-09-05T05:11:18.2866667+00:00 The above query showing wrong results only in case of BLOB from oracle
Wrong in which way? What's the BLOB content and how do you verify it's "wrong"?
-
Nawaz 1 Reputation point
2023-09-05T05:18:16.9733333+00:00 End users can see the BLOB in their application. They see different or mismatch results for BLOB columns .
-
Erland Sommarskog 110.3K Reputation points2023-09-05T22:01:45.7533333+00:00 And what is "different or mismatch results for BLOB columns". You need to be more specific.
I have very little idea of what might be going on, but one thing that comes to mind is byte-ordering. SQL Server is on Wintel architecture that is Little Endian, and if the data comes from an Big Endian architecture, interesting things could maybe happen.
Sign in to comment
Sign in to answer