Issues with updating many rows oracle


18-Nov-2015 14:41

Do not post product-related questions in the feedback space; instead, navigate to or search for an appropriate product community to post them in. 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. Fieldx AND Table ZZZ.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.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 (sqora32version 11.2.0.1 with version 11.1.0.7), and the problem went away.I then used database or SQLNet tracing to see what the ODBC driver was handing off to SQLNet/database.

Here is a simple example: UPDATE Table X SET Table X. 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. 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 11g R1, I would love to find it.



That means both pairs of dancers earned two perfects on the night!… continue reading »


Read more