July 14, 2020 at 9:42 am
Hi all,
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 (12.2.0.1.0) all the way up to the latest available driver release. The last version working correctly is 12c Release 1 (12.1.0.2.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!
July 14, 2020 at 5:27 pm
First - I have not seen this issue before, but we are a fully SQL Server shop where I work without any Oracle boxes.
When you say it works correctly in 12c Release 1 (12.1.0.2.0), is there any reason you can't use that driver?
Does your Oracle Driver version match your Oracle Database version? If not, I would make sure those match. It could be an optimization put in the driver for newer Oracle versions. I can't speak for Oracle, but I know with SSIS if the SSMS version doesn't match, when you push up a new SSIS package into an SSIS catalog from a DTSX file (converted to ispac), it will push it as the wrong version and be unusable.
Failing that, I would look into making an Oracle support account and get them to help with it.
My habit though has always been to try to match versions for things where possible, so if it was me, I'd have my Oracle driver match the Oralce database versions as closely as possible.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 15, 2020 at 7:13 am
It does not match the database version, db version is 18c but when we use the matching driver version we are running into this issue. So we had to go all the way back to this working version. I do not have any specific reason not to use to use that old version, except it is old and we would like to match the driver with the db version 🙂
Yes I'll try to get in contact with Oracle, Thanks!
September 5, 2020 at 8:46 pm
In the remote query, check the quotes around the name column are these double-quotes or single?
What happens if you add order by clause to the above queries?
=======================================================================
September 14, 2020 at 7:54 am
An update if anyone ever finds this post..
I have had contact with Oracle support a lot about this issue after I made this post.
And after doing tests we found out the ole db driver actually does returns the correct results, we tested this by running some Vb script which directly outputs the result from the ole db driver, and to my surprise the rows were correct.
So they point to SQL server... apparently the sql linked server messes up the results, but only when using the newer Oracle drivers... So I'm kind of lost for a solution and we are simply using the older Oracle driver now.
Cheers.
September 15, 2020 at 9:48 pm
Hi Henk West,
Is there any other Oracle driver you can install and use? If so, maybe give that a shot
As a possible workaround, have you tried downloading two sets of data. One set has all of the values minus the binary data and the other set has the ID plus binary. Once both are downloaded, you can just join the two datasets together.
*** Please network with me and feel free to IM if you have any more DBA questions and concerns
https://www.linkedin.com/in/james-rossi-9094651b7/
james rossi
Senior SQL Server DBA
Senior Software Developer
Alpha One Data
Brain Box SQL Mobile DBA
September 15, 2020 at 10:01 pm
I'm thinking that the data has an embedded Cr, Lf, or Vertical Tab in it and OPENQUERY along with the given provider is treating what follows that as a separate row.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2020 at 4:27 pm
Just a quick note....
If you draw in the whole table normally...
sample:
FROM OPENQUERY([HEPRD-COURSES],
'SELECT * FROM PS_ZVSR_CLASS_NOTE') [zClassNote]
try casting the offending field
sample2:
FROM OPENQUERY([HEPRD-COURSES],
'SELECT CLASS_NOTES_SEQ, CAST(SUBSTR(DESCRLONG,1,4000) AS VARCHAR2(4000)) as DESCRLONG, CRSE_ID, CRSE_OFFER_NBR, STRM, SESSION_CODE, CLASS_SECTION FROM PS_ZVSR_CLASS_NOTE') [zClassNote]
just as a test...
Thanks
*separate note sent*
November 16, 2020 at 8:36 pm
Hi Henk,
Were you ever able to resolve this issue?
Thanks!
Shane
November 17, 2020 at 10:22 am
Hello,
No, just the workaround of installing the very old driver for now.
I no longer have an environment with above specs to reproduce the issue. I do think the mentioned steps could very well solve the issue. I noticed the jump in rows always happened at a certain text block. It could very well have some embedded chars / line breaks in it, making sql server mess up the rows.
Unfortunately I don't have access to such environment atm and unable to reinstall the newer drivers due to production system. Once I do I will update.
Thanks, Henk
November 17, 2020 at 7:57 pm
Thanks Henk! We are currently having the same issue and are going to rollback the driver to see if that fixes the issue. I will post if that works. We did also have an ORACLE upgrade to 19c that same night so we weren't sure if that was the issue or the driver update was.
Btw, the cast solution that millerm posted does resolve the incorrect column issues.
Thanks!
November 19, 2020 at 4:54 pm
Rolling back the driver did work. It fixed the mixed up CLOB column issues and several issues on jobs taking a long time and then failing regardless of if tables had LOB data type columns.
Thanks!
November 19, 2020 at 8:04 pm
Jeff, Your suggestion has plausibility. My question to you would be, if those characters are the problem, why does it only happen on the new versions of the driver?
We are in the same situation. We had been using driver 12c (12.1.0.2) for several years with no problem. On the evening of Nov 3rd, I upgraded to 12.2.0.1. When the download processes started back up at 4 am the next morning, they were running slower. Only every other run would complete. The next run would email "a failure/still running" message.
Last night I rolled the driver back to 12.1.0.2 and the jobs are completing as expected.
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
November 19, 2020 at 8:38 pm
Caution: stay away from the new driver(12.2). about one year ago, I used it first, SSIS consumed 30GB memory, which was almost all the memory on the box; I switched to 12.1, SSIS consumed < 1GB. I found this before switching to 12.1:
November 19, 2020 at 9:08 pm
Henk, Did you open a ticket with Microsoft about the issue?
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply