Oracle linked server mixing up result values

  • Henk West

    Old Hand

    Points: 386

    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!

    • This topic was modified 2 months, 1 week ago by  Henk West.
    Attachments:
    You must be logged in to view attached files.
  • Mr. Brian Gale

    SSC-Insane

    Points: 23167

    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.

  • Henk West

    Old Hand

    Points: 386

    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!

  • IrfanHyd

    SSCommitted

    Points: 1619

    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?

    ---------------------------------------------------------------------------------------------------------------------------------
    IrfanHyd

  • Henk West

    Old Hand

    Points: 386

    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.

  • James Rossi

    Grasshopper

    Points: 21

    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

  • Jeff Moden

    SSC Guru

    Points: 997150

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • millerm

    Newbie

    Points: 3

    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*

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply