This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 8, 2013 7:42 AM by user415250 RSS

Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)

779256 Newbie
Currently Being Moderated
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 = “valuex” WHERE TableX.Fieldx = TableZZZ.Fieldx AND TableZZZ.fieldzzz is not null

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.

The results are in the following post:

Thanks guys!!
  • 1. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    779256 Newbie
    Currently Being Moderated
    ODBC TRACE
    ---------------------------------------------------

    14b4-14b8     ENTER SQLBindParameter
              HSTMT 09D03020
              UWORD 1
              SWORD 1 <SQL_PARAM_INPUT>
              SWORD -8 <SQL_C_WCHAR>
              SWORD 3 <SQL_DECIMAL>
              SQLULEN 8
              SWORD 0
              PTR 0x0DFA2224
              SQLLEN 0
              SQLLEN * 0x0DFA2220

    14b4-14b8     EXIT SQLBindParameter with return code 0 (SQL_SUCCESS)
              HSTMT 09D03020
              UWORD 1
              SWORD 1 <SQL_PARAM_INPUT>
              SWORD -8 <SQL_C_WCHAR>
              SWORD 3 <SQL_DECIMAL>
              SQLULEN 8
              SWORD 0
              PTR 0x0DFA2224
              SQLLEN 0
              SQLLEN * 0x0DFA2220 (4)

    14b4-14b8     ENTER SQLBindParameter
              HSTMT 09D03020
              UWORD 2
              SWORD 1 <SQL_PARAM_INPUT>
              SWORD 99 <SQL_C_DEFAULT>
              SWORD 3 <SQL_DECIMAL>
              SQLULEN 8
              SWORD 0
              PTR 0x0013CF98
              SQLLEN 0
              SQLLEN * 0x0013CF94

    14b4-14b8     EXIT SQLBindParameter with return code 0 (SQL_SUCCESS)
              HSTMT 09D03020
              UWORD 2
              SWORD 1 <SQL_PARAM_INPUT>
              SWORD 99 <SQL_C_DEFAULT>
              SWORD 3 <SQL_DECIMAL>
              SQLULEN 8
              SWORD 0
              PTR 0x0013CF98
              SQLLEN 0
              SQLLEN * 0x0013CF94 (1)

    *14b4-14b8     ENTER SQLExecute*
              HSTMT               09D03020
    *14b4-14b8     EXIT SQLExecute with return code -1 (SQL_ERROR)*
              HSTMT               09D03020
              *DIAG [22005] [Oracle][ODBC][Ora]ORA-01722: invalid number*
    *(1722)*
    14b4-14b8     ENTER SQLErrorW
              HENV 09D01788
              HDBC 09D01830
              HSTMT 09D03020
              WCHAR * 0x0013CCB8 (NYI)
              SDWORD * 0x0013CC84
              WCHAR * 0x0827C000
              SWORD 4095
              SWORD * 0x0013CC78

    14b4-14b8     EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
              HENV 09D01788
              HDBC 09D01830
              HSTMT 09D03020
              WCHAR * 0x0013CCB8 (NYI)
              SDWORD * 0x0013CC84 (1722)
              WCHAR * 0x0827C000 [      90] "[Oracle][ODBC][Ora]ORA-01722: invalid number\ a\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0"
              SWORD 4095
              SWORD * 0x0013CC78 (90)

    14b4-14b8     ENTER SQLErrorW
              HENV 09D01788
              HDBC 09D01830
              HSTMT 09D03020
              WCHAR * 0x0013CCB8 (NYI)
              SDWORD * 0x0013CC84
              WCHAR * 0x0827C072
              SWORD 4038
              SWORD * 0x0013CC78

    14b4-14b8     EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
              HENV 09D01788
              HDBC 09D01830
              HSTMT 09D03020
              WCHAR * 0x0013CCB8 (NYI)
              SDWORD * 0x0013CC84
              WCHAR * 0x0827C072
              SWORD 4038
              SWORD * 0x0013CC78

    14b4-14b8     ENTER SQLCancel
              HSTMT 09D03020
  • 2. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    779256 Newbie
    Currently Being Moderated
    SQLNET TRACE
    If you want an Admin level trace, I can have one right away.
    --------------------------------------------

    (856) [13-JUN-2010 22:11:00:657] nsopen: opening transport...
    (856) [13-JUN-2010 22:11:00:657] nttcni: Tcp conn timeout = 60000 (ms)
    (856) [13-JUN-2010 22:11:00:657] nttcni: trying to connect to socket 1364.
    (856) [13-JUN-2010 22:11:00:688] nttcni: connected on ipaddr 142.139.221.62
    (856) [13-JUN-2010 22:11:00:688] nttcon: set TCP_NODELAY on 1364
    (856) [13-JUN-2010 22:11:00:688] nsopen: transport is open
    (856) [13-JUN-2010 22:11:00:688] nsnainit: inf->nsinfflg[0]: 0x61 inf->nsinfflg[1]: 0x61
    (856) [13-JUN-2010 22:11:00:688] nsopen: global context check-in (to slot 0) complete
    (856) [13-JUN-2010 22:11:00:688] nscon: doing connect handshake...
    (856) [13-JUN-2010 22:11:00:688] nscon: sending NSPTCN packet
    (856) [13-JUN-2010 22:11:00:688] nscon: sending 233 bytes connect data
    (856) [13-JUN-2010 22:11:00:688] nsdo: 233 bytes to NS buffer
    (856) [13-JUN-2010 22:11:00:719] nscon: got NSPTRS packet
    (856) [13-JUN-2010 22:11:00:719] nscon: sending NSPTCN packet
    (856) [13-JUN-2010 22:11:00:719] nscon: sending 233 bytes connect data
    (856) [13-JUN-2010 22:11:00:719] nsdo: 233 bytes to NS buffer
    (856) [13-JUN-2010 22:11:00:735] nscon: got NSPTAC packet
    (856) [13-JUN-2010 22:11:00:735] nscon: connect handshake is complete
    (856) [13-JUN-2010 22:11:00:735] nscon: nsctxinf[0]=0x61, [1]=0x21
    (856) [13-JUN-2010 22:11:00:735] nsnainconn: inf->nsinfflg[0]: 0x61 inf->nsinfflg[1]: 0x21
    (856) [13-JUN-2010 22:11:00:735] nsnasend: bytes to send: 158
    (856) [13-JUN-2010 22:11:00:735] nsdo: 158 bytes to NS buffer
    (856) [13-JUN-2010 22:11:00:735] nsnareceive: buffer address: 0x132c34 bytes wanted: 2048
    (856) [13-JUN-2010 22:11:00:735] nsnareceive: calling NS to receive 2048 bytes into address 0x132c34
    (856) [13-JUN-2010 22:11:00:766] nsdo: 153 bytes from NS buffer
    (856) [13-JUN-2010 22:11:00:766] nsnareceive: received 153 bytes
    (856) [13-JUN-2010 22:11:00:766] nsnareceive: no more data to receive - returning
    (856) [13-JUN-2010 22:11:00:766] nsnareceive: total bytes received: 153
    (856) [13-JUN-2010 22:11:01:063] nsnasend: bytes to send: 77
    (856) [13-JUN-2010 22:11:01:063] nsdo: 77 bytes to NS buffer
    (856) [13-JUN-2010 22:11:01:063] nsnareceive: buffer address: 0x132c34 bytes wanted: 2048
    (856) [13-JUN-2010 22:11:01:063] nsnareceive: calling NS to receive 2048 bytes into address 0x132c34
    (856) [13-JUN-2010 22:11:01:079] nsdo: 64 bytes from NS buffer
    (856) [13-JUN-2010 22:11:01:079] nsnareceive: received 64 bytes
    (856) [13-JUN-2010 22:11:01:079] nsnareceive: no more data to receive - returning
    (856) [13-JUN-2010 22:11:01:079] nsnareceive: total bytes received: 64
    (856) [13-JUN-2010 22:11:01:079] naun5authent: Authentication type is 0
    (856) [13-JUN-2010 22:11:01:079] nsnasend: bytes to send: 1862
    (856) [13-JUN-2010 22:11:01:079] nsdo: 1862 bytes to NS buffer
    (856) [13-JUN-2010 22:11:01:079] nsnareceive: buffer address: 0x132c34 bytes wanted: 2048
    (856) [13-JUN-2010 22:11:01:079] nsnareceive: calling NS to receive 2048 bytes into address 0x132c34
    (856) [13-JUN-2010 22:11:01:141] nsdo: 165 bytes from NS buffer
    (856) [13-JUN-2010 22:11:01:141] nsnareceive: received 165 bytes
    (856) [13-JUN-2010 22:11:01:141] nsnareceive: no more data to receive - returning
    (856) [13-JUN-2010 22:11:01:141] nsnareceive: total bytes received: 165
    (856) [13-JUN-2010 22:11:01:141] nsnasend: bytes to send: 33
    (856) [13-JUN-2010 22:11:01:141] nsdo: 33 bytes to NS buffer

    These lines are present using both version of sqora32.dll

    (856) [13-JUN-2010 22:11:01:141] nszgwop: SQLNET.WALLET_OVERRIDE not found, using default.
    (856) [13-JUN-2010 22:11:01:157] nscontrol: Vect I/O support: 0(856) [13-JUN-2010 22:11:01:391] nioqrc: Recieve: returning error: 3111
    (856) [13-JUN-2010 22:11:01:391] nsdo: sending NSPTMK packet
    (856) [13-JUN-2010 22:11:01:391] nserror: nsres: id=0, op=77, ns=12630, ns2=0; nt[0]=0, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0

    These lines only happen when using the R2 version of sqora32.dll

    (856) [13-JUN-2010 22:11:01:719] nioqrc: Recieve: returning error: 3111
    (856) [13-JUN-2010 22:11:01:719] nsdo: sending NSPTMK packet
    (856) [13-JUN-2010 22:11:01:860] nserror: nsres: id=0, op=0, ns=12630, ns2=0; nt[0]=0, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0

    (856) [13-JUN-2010 22:21:03:782] nstimarmed: no timer allocated
  • 3. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    779256 Newbie
    Currently Being Moderated
    By the way - the query is actuallly more like this:

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

    So again, this dosn't work (anymore) from Access on linked tables, but it does work in sqlPlus. It will work from Access if I am using the 11r1 version of the Oracle ODBC drive, but when I swap it for the 11r2 version, it fails in the ways described above.

    Also, running the same type of update query in Access on linked tables in a SQL Server database works.
  • 4. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    166890 Newbie
    Currently Being Moderated
    ...we had exactly the same thing running version 11.2.0.1.0
    The temporary solution was to install instant client 11.1.0.7.0 (which works fine)

    Cheers
    Fredy
  • 5. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    779256 Newbie
    Currently Being Moderated
    f_tschui wrote:
    ...we had exactly the same thing running version 11.2.0.1.0
    The temporary solution was to install instant client 11.1.0.7.0 (which works fine)

    Cheers
    Fredy
    Thanks for the reply. Yes, the R1 odbc driver works fine. Unfortunately, reverting all our clients back to that is not really an option., and it's also not really any kind of solution. I am very, very surprised that more people have not commented on this..
  • 6. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    166890 Newbie
    Currently Being Moderated
    ...sure, the problem is not really solved with 11.1

    i noticed another very strange thing in 11.2, a simple select query on a single table does not return the correct result:

    SELECT Emp.ID FROM Emp WHERE UCase([Name]) Like 'ARME*'; --> returns 5 records, so far so good
    SELECT Emp.ID FROM Emp WHERE UCase([Name]) Like 'ARM*'; --> returns 2 records, ...i've absolutly no idea why

    also, we had some another messages like "ORA-01460: unimplemented or unreasonable conversion requested" occured in update/delete queries with date/time parameters (also worked fine in 11.1.0.7.0)

    --> too many problems this time with 11.2, hope there will be a bug fix soon


    PS: we're using NLS_LANG=GERMAN_SWITZERLAND

    Edited by: f_tschui on 24.06.2010 02:10
  • 7. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    779256 Newbie
    Currently Being Moderated
    Thanks f_tschui, I appreciate you sharing that - it's somewhat comforting to know that others are experiencing similar issues :)
    I hope they come out with a hotfix soon, or at least acknowledge that there is currently an issue.
  • 8. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    gdarling - oracle Expert
    Currently Being Moderated
    Hi,

    I don't see where this has already been logged as a bug, so I'd recommend opening a SR with support so we can get one filed.

    Just as kind of a "red flag", if this seems related to NLS settings on the client, there's a "Numeric Settings" option in the DSN config you might want to try changing. You'll need to refresh the table links in MSAccess after any changes, as the JET caches DSN settings when the table was first linked.

    Hope it helps,
    Greg
  • 9. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    779256 Newbie
    Currently Being Moderated
    hmm, I have already tried changing the numeric settings in the dns config but Access would simply crash after making any change. Maybe this was because I wasn't relinking the tables? I'll give this a try. Thanks.
  • 10. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    gbednars Newbie
    Currently Being Moderated
    This behavior appears to be bug 9903704 which is actively being worked by Oracle development. Once it has been fixed and the patch applied these issues should be resolved. Until then you will have to use an 11.1 or earlier ODBC driver and Oracle Client. No, I do not know when this bug will be fixed. Watch it using MOS and the bug number. Once it has been fixed it will be backported to 11.2 and a patch with the fix will come out. Its that patch you will need to apply to resolve this issue on 11.2.
  • 11. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    804065 Newbie
    Currently Being Moderated
    I can't find a link to the status of this BUG anywhere on oracle.com or the internet
  • 12. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    561565 Newbie
    Currently Being Moderated
    Try support.oracle.com and search for bug 9903704 or Article 1244424.1. Patch set 7 for 11.2 has the updated ODBC files. Thanks to others in this thread for initially reporting this. Nice to be able to search for this issue and find the fix.
  • 13. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    877142 Newbie
    Currently Being Moderated
    Has anyone seen this issue in 11.2.0.1.0 ? 32/64

    Win7 64

    We are seeing the same messages, but have the latest version installed?
  • 14. Re: Oracle 11gr2 ODBC - error updating linked table (Ora 01722 and 01461)
    897966 Newbie
    Currently Being Moderated
    Wow! Thanks for discussing this. I had the very same problem when I "upgraded" our Instant Client ODBC driver from 10.1.0.3 to 11.2.0.2 and I was really pulling my hair out on why all my data was corrupted. Thanks also for mentioning the bug number. Looking that up... as of today... it says fixed in product version 12.1. Which must be Oracle's way of saying "will be fixed in a future release" because there is no Instant Client ODBC 12.1 driver.

    Anyhow... thanks.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points