11.2.0.1 ODBC Update Problem

2112010

November 2, 2010 (Updated November 3, 2010)

An interesting ODBC problem was brought to my attention yesterday regarding the Microsoft Access 2010 program and the Oracle Database 11.2.0.1 ODBC client. What is the problem? The person reported that they were trying to update a table in a 10.2.0.4 Oracle Database using values from a database table that resides in an Microsoft Access 2010 database – Microsoft Access was supposed to magically update the Oracle table when the user issued a simple UPDATE statement.

I put together a test case using a 11.2.0.1 Oracle Database to simulate the problem, picking a table named PART in the Oracle Database and creating a table named UPDATE_TABLE in Microsoft Access 2010. I then constructed the following SQL statement:

UPDATE
PART
SET
WHSALE_UNIT_COST = (
SELECT
UPDATE_TABLE.WHSALE_UNIT_COST
FROM
UPDATE_TABLE
WHERE
UPDATE_TABLE.PART_ID = PART.ID)
WHERE
PART.ID IN (
SELECT
PART_ID
FROM
UPDATE_TABLE);

No error 3073 this time, so the above must be a valid SQL statement. I put 4 test rows into the UPDATE_TABLE table and let the update run:

Great, its going to work… Hey, why did I receive a warning that 3 records could not be updated due to lock violations? Darn Microsoft bugs!

Let’s try to execute the SQL statement again:

LONGs? The PART.ID column is defined as VARCHAR2(30), the PART.WHSALE_UNIT_COST is defined as NUMBER(20,6). Maybe it is the Access table’s fault. The UPDATE_TABLE.PART_ID column is defined as TEXT(30), and it does not matter if the UPDATE_TABLE.WHSALE_UNIT_COST column is defined as DECIMAL, SINGLE, or DOUBLE.

For fun, let’s create a trigger to enable a 10046 trace at level 12 whenever the Microsoft Access program connects to the database. The trigger at the top of this blog article will work without modification. Now let’s take a look at part of the 10046 trace file after trying the update SQL statement again:

Notice in the above that the bind variable value for the PART.ID column was left as NULL – that can’t work if we are trying to update rows.

So, I guess that explains why 3 of the 4 updates failed. Darn Microsoft bugs! But wait, here is an interesting thread on a Microsoft site that suggests the problem is with the 11.2.0.1 client’s ODBC dll named sqora32.dll. Someone in the thread suggested just borrowing the sqora32.dll file from a 11.1.0.7 client install. I was suspicious about the advice, so I borrowed the sqora32.dll file from a 10.2.0.1 client install. I have seen this before, Yes.

And then… nothing. The 10046 logon trigger was still enabled, let’s check the trace file:

It worked! The last line indicates that the changes were committed, and all bind variables were passed in. So, I tested again with the sqora32.dll file from a 11.1.0.7 client – it still worked. I then went back to the sqora32.dll file from the 11.2.0.1 client, and the failure messages returned. OK, maybe the problem is with the 11.2.0.1 Oracle Database? Pointing Access 2010 at a 10.2.0.4 Oracle Database with the sqora32.dll file from the 11.2.0.1 client returned the same silly error messages that I saw earlier.

What do you know, it is not a Microsoft Bug 2010 after all.

——————-

Edit: November 3, 2010

A search of Metalink (MOS) revealed that Patch 7 for Oracle Database/Client 11.2.0.1 on the Windows platform included a fix for the ODBC problem – this fix is not included in Patch 6 for 11.2.0.1. Metalink indicated that patch number 10155837 is Patch 7 for Oracle 11.2.0.1, but that is only for the 32 bit Oracle binaries on Windows. A patch search found patch number 10155838 which appears to include the same fix for the 64 bit Oracle binaries on Windows. Initial testing of the 32 bit patch (10155837) on a 32 bit client home residing on a 64 bit Windows client seems to show that the problem is fixed by patch 10155837. From the 10046 trace file:

Joel, thank you for finding that bug report – it certainly seems to describe the problem. I wonder if these are slightly different bugs? One small difference is that the Metalink bug that you found describes a problem that resulted in ORA-01460, while the above problem shows a problem that resulted in ORA-01461.

Working with Joel’s suggestion I searched Metalink for 11.2.0.1 ora-1461
That search found Doc ID 1244424.1
Just need to find Oracle 11.2.0.1 patch 7, patch number 10155837 (Edit Nov 3, 2010: patch number 10155837 is for 32 bit Oracle binaries on Windows, while patch number 10155838 appears to be for 64 bit Oracle binaries on Windows)… and on to the next bug. :-)

(Edit: Nov 3, 2010: I don’t know if someone at Oracle support saw this blog post yesterday, but Metalink Doc ID 1262530.1 was added on Nov 2, 2010 with a brief test case – I am fairly sure that this Doc ID was not visible when I posted this blog article. That Doc ID also recommends installing the 11.2.0.1 patch 7.)

Yeah, I thought to look for the right error number after I posted, then saw it was on the search I mentioned, and the drill down from there to the base bug was for something a month or two ago IIRC, so there we be. I can’t help but think there are a lot of bugs burbling about just under the surface, that get exposed when people ask about them (or maybe even search for them? Could someone at Oracle be using analytics to watch what gets searched and act on it?). I can’t help wanting to berate Oracle QA when they put out something so brazenly broken, in this case whether it breaks a bind variable or corrupts an update, somebody ought to catch it earlier than production users. Whatever happened to beta testers? Why isn’t this regression tested? There are just too many interlocking parts to systems these days to be so sloppy.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: