I am having a very strange issue with Oracle linked servers. The linked server is created based on the Oracle Ole DB provider. I can reproduce this issue on any SQL server instance.
Whenever I retrieve somewhere around 500-600+ rows and large binary data is included the returned rows are mixed up: meaning the column value actually belongs to a different row. I have been able to reproduce this issue starting from Oracle driver 12c Release 2 (22.214.171.124.0) all the way up to the latest available driver release. The last version working correctly is 12c Release 1 (126.96.36.199.0).
In the attached image you can see the issue, both queries should return the same value, however when I put the condition on the SQL server side of the openquery retrieving all rows from Oracle, the value is different. It seems truncated but when I convert the binary value with our tool I can see it actually is a correct value belonging to a different row. The value that should have been returned is found on a different row.
It seems to happen mostly when binary data is included but I have seen the same issue with large text field.
Update: I have reproduced the same issue with large text fields (CLOB in Oracle), as you can see in the image the description result is different for the same record and actually belongs to a different record in the table.
Unfortunately I do not have Oracle support as it seems to be an Oracle issue but I was wondering if someone maybe encountered this issue also, and if perhaps some driver setting could fix it? Or some other workaround instead of using the old driver version?
Any help would be appreciated, thanks!
- This topic was modified 2 months, 1 week ago by Henk West.