locked
Error updating linked table RRS feed

  • Question

  • Hello folks,

    I have posted the following issue on the Oracle Forums, but have not got any feedback. I was wondering if perhaps any of you have run into the same issue and could perhaps share some insight with me? Thanks very much!

    ---------------------------------------------------------------

    Good day folks,
    My shop has just moved to 11gR2 client and server. We were previously using 11gR1 with no issues (and before that, 10, 9, 8, etc). After moving from 11r1 to 11r2, we began getting errors from some of our MS Access ODBC applications with linked Oracle tables. The error would occur when executing an UPDATE statement that had a table join in it. Here is a simple example:

    UPDATE tablex SET tablex.fieldx = value
    WHERE tablex.primaryKeyField IN (SELECT tableZ.primaryKeyField from tableZ where (some filter...));


    Currently, after moving to 11r2 client, an update query like the one above will error out in one of the following ways:
    - odbc -- update on a linked table failed - Ora 01722 invalid number
    - ORA-01461: can bind a LONG value only for insert into a LONG column
    - Or it will say that the records were not updated because they are locked.

    In some cases, I have noticed some records being updated that were not supposed to be updated.. records that the where clause was meant to exclude. That is very unsettling.

    I understand that perhaps an update statement shouldn’t be joining table and perhaps it should be done over a couple calls, but the reality is – this code is out there in abundance and if there is a solution that doesn’t amount to my changing all this code or reverting to 11gR1, I would love to find it.

    Since the query runs fine using SQL Plus and also runs fine if I run it against a local table in Access rather than a linked Oracle table – I figured the issue was possibly with the Oracle 11r2 ODBC driver. So, I switched the Oracle ODBC driver (sqora32.dll version 11.2.0.1 with version 11.1.0.7), and the problem went away.
    I believe this verifies the issue resides with Oracle ODBC version 11.2.0.1. Can anyone help? I'm assuming it's not particularly wise to simply swap sqora32.dll files on all my clients machines, so I am searching for an actual solution here instead.

    I also did performed ODBC tracing to see what Access is handing to the Oracle ODBC driver. I then used database or SQLNet tracing to see what the ODBC driver was handing off to SQLNet/database. If you wish to see the log, let me know!

    Wednesday, June 16, 2010 4:43 PM

Answers

  • Hello adamnb1,

    I agree that problems you are facing after moving to 11gR2 are due to ODBC driver. I am curious what did you do saying "I switched the Oracle ODBC driver (sqora32.dll version 11.2.0.1 with version 11.1.0.7)".

    Did you create a new DataSource based on 11.1.0.7 version and re-link the tables, or you did something else?

    Nadia


    Hi,

    I downloaded the instant client for R1, which containts the R1 odbc library (sqora32.dll - version 11.1.0.7). I then swaped the R2 odbc library (C:\oracle\Client\11gR2\BIN\sqora32.dll) with the R1 version. It's crude,. but was the fasted way to troubleshoot. I have actually be running with the old ddl version on my machine for a while now with no issue... but I certinaly don't consider that a solution of any kind. There are obviously bugs in the R2 version. Suppose I'll need to wait for the hotfix from Oracle.  

    • Marked as answer by Ji.Zhou Wednesday, June 30, 2010 1:45 AM
    Thursday, June 24, 2010 4:59 PM

All replies

  • Hello adamnb1,

    I agree that problems you are facing after moving to 11gR2 are due to ODBC driver. I am curious what did you do saying "I switched the Oracle ODBC driver (sqora32.dll version 11.2.0.1 with version 11.1.0.7)".

    Did you create a new DataSource based on 11.1.0.7 version and re-link the tables, or you did something else?

    Nadia

    Saturday, June 19, 2010 9:18 PM
  • Hello adamnb1,

    I agree that problems you are facing after moving to 11gR2 are due to ODBC driver. I am curious what did you do saying "I switched the Oracle ODBC driver (sqora32.dll version 11.2.0.1 with version 11.1.0.7)".

    Did you create a new DataSource based on 11.1.0.7 version and re-link the tables, or you did something else?

    Nadia


    Hi,

    I downloaded the instant client for R1, which containts the R1 odbc library (sqora32.dll - version 11.1.0.7). I then swaped the R2 odbc library (C:\oracle\Client\11gR2\BIN\sqora32.dll) with the R1 version. It's crude,. but was the fasted way to troubleshoot. I have actually be running with the old ddl version on my machine for a while now with no issue... but I certinaly don't consider that a solution of any kind. There are obviously bugs in the R2 version. Suppose I'll need to wait for the hotfix from Oracle.  

    • Marked as answer by Ji.Zhou Wednesday, June 30, 2010 1:45 AM
    Thursday, June 24, 2010 4:59 PM
  • Adamnb1,

    Thank you for your feedback. Hopefully, Oracle will resolve this issue.

    Nadia

    Friday, June 25, 2010 10:48 PM