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);
Nifty, except that the above SQL statement generated an error message:
Operation must use an updatable query (Error 3073)
OK, let’s try this crazy looking SQL statement instead:
UPDATE DISTINCTROW PART INNER JOIN UPDATE_TABLE ON PART.ID = UPDATE_TABLE.PART_ID SET PART.WHSALE_UNIT_COST = UPDATE_TABLE.WHSALE_UNIT_COST;
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:
PARSING IN CURSOR #4 len=64 dep=0 uid=287 oct=6 lid=287 tim=20065405443 hv=655341070 ad='4696fd8f0' sqlid='gwux0ysmhzdhf' UPDATE "TESTUSER"."PART" SET "WHSALE_UNIT_COST"=:1 WHERE "ID" = :2 END OF STMT PARSE #4:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1148805963,tim=20065405443 BINDS #4: Bind#0 oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0 kxsbbbfp=1f284498 bln=2000 avl=07 flg=05 value="123.122" Bind#1 oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0 kxsbbbfp=1f29f060 bln=4000 avl=09 flg=05 value=".250-A572" EXEC #4:c=0,e=264,p=0,cr=3,cu=4,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=20065405739
The above kind of works, even though the number value is passed in as a VARCHAR2 rather than as a NUMBER. The WHSALE_UNIT_COST will be set to 123.122 for part ID .250-A572.
Now the next set of bind variables:
BINDS #4: Bind#0 oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0 kxsbbbfp=1f284498 bln=2000 avl=07 flg=05 value="4554.44" Bind#1 oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0 kxsbbbfp=1f29f060 bln=4000 avl=00 flg=05 EXEC #4:c=0,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1148805963,tim=20065407265
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.
The next set of bind variables:
BINDS #4: Bind#0 oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0 kxsbbbfp=1f284498 bln=2000 avl=07 flg=05 value="45.1¿¿" Bind#1 oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0 kxsbbbfp=1f29f060 bln=4000 avl=00 flg=05 EXEC #4:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1148805963,tim=20065408596
Notice in the above that the bind variable value for the PART.ID column was left NULL, and the value passed in for the WHSALE_UNIT_COST is 45.1¿¿ – I am pretty sure that is an invalid number.
The final set of bind variables:
BINDS #4: Bind#0 oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0 kxsbbbfp=1f284498 bln=2000 avl=07 flg=05 value="665" Bind#1 oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0 kxsbbbfp=1f29f060 bln=4000 avl=00 flg=05 EXEC #4:c=0,e=158,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1148805963,tim=20065410013
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:
PARSING IN CURSOR #4 len=64 dep=0 uid=287 oct=6 lid=287 tim=28794270809 hv=655341070 ad='4696fd8f0' sqlid='gwux0ysmhzdhf' UPDATE "TESTUSER"."PART" SET "WHSALE_UNIT_COST"=:1 WHERE "ID" = :2 END OF STMT PARSE #4:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1148805963,tim=28794270808 BINDS #4: Bind#0 oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0 kxsbbbfp=1f2af830 bln=2000 avl=10 flg=05 value="123.122001" Bind#1 oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0 kxsbbbfp=1f2ae850 bln=4000 avl=09 flg=05 value=".250-A572" EXEC #4:c=0,e=324,p=0,cr=3,cu=4,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=28794271179 ... BINDS #4: Bind#0 oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0 kxsbbbfp=1f2abb40 bln=2000 avl=11 flg=05 value="4554.443847" Bind#1 oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0 kxsbbbfp=1f2af060 bln=4000 avl=09 flg=05 value=".250X.750" EXEC #4:c=0,e=181,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=28794292695 ... BINDS #4: Bind#0 oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0 kxsbbbfp=1f2abb40 bln=2000 avl=09 flg=05 value="45.099998" Bind#1 oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0 kxsbbbfp=1f2af060 bln=4000 avl=07 flg=05 value=".50-A36" EXEC #4:c=0,e=198,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=28794312262 ... BINDS #4: Bind#0 oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0 kxsbbbfp=1f2abb40 bln=2000 avl=03 flg=05 value="665" Bind#1 oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0 kxsbbbfp=1f2af060 bln=4000 avl=08 flg=05 value="00045364" EXEC #4:c=0,e=217,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=28794333188 WAIT #4: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=28794333220 *** 2010-11-02 14:02:01.661 WAIT #4: nam='SQL*Net message from client' ela= 2750115 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=28797083349 XCTEND rlbk=0, rd_only=0, tim=28797083480
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:
PARSING IN CURSOR #7 len=64 dep=0 uid=287 oct=6 lid=287 tim=7180612054 hv=655341070 ad='4697fba58' sqlid='gwux0ysmhzdhf' UPDATE "TESTUSER"."PART" SET "WHSALE_UNIT_COST"=:1 WHERE "ID" = :2 END OF STMT PARSE #7:c=0,e=85,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=7180612053 ... BINDS #7: Bind#0 oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0 kxsbbbfp=203f0bc8 bln=2000 avl=10 flg=05 value="123.122001" Bind#1 oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0 kxsbbbfp=203f1ab0 bln=4000 avl=09 flg=05 value=".250-A572" ... EXEC #7:c=31200,e=45322,p=17,cr=628,cu=4,mis=1,r=1,dep=0,og=1,plh=1148805963,tim=7180657412 ... BINDS #7: Bind#0 oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0 kxsbbbfp=203f0fe0 bln=2000 avl=11 flg=05 value="4554.443847" Bind#1 oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0 kxsbbbfp=203f84e8 bln=4000 avl=09 flg=05 value=".250X.750" EXEC #7:c=0,e=247,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=7180678947 ... BINDS #7: Bind#0 oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0 kxsbbbfp=203f0fe0 bln=2000 avl=09 flg=05 value="45.099998" Bind#1 oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0 kxsbbbfp=203f1ab0 bln=4000 avl=07 flg=05 value=".50-A36" EXEC #7:c=0,e=173,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=7180699090 ... BINDS #7: Bind#0 oacdty=01 mxl=2000(2000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=2000 off=0 kxsbbbfp=203f0fe0 bln=2000 avl=03 flg=05 value="665" Bind#1 oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00 oacflg=07 fl2=1000010 frm=01 csi=178 siz=4000 off=0 kxsbbbfp=203f84e8 bln=4000 avl=08 flg=05 value="00045364" EXEC #7:c=0,e=164,p=0,cr=3,cu=2,mis=0,r=1,dep=0,og=1,plh=1148805963,tim=7180720112 WAIT #7: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=7180720145 *** 2010-11-03 08:03:08.775 WAIT #7: nam='SQL*Net message from client' ela= 2522305 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=7183242464 XCTEND rlbk=0, rd_only=0, tim=7183242564
The 10046 trace file show four complete sets of bind variables and a successful COMMIT at the end.
Charles Hooper's Oracle Notes 
45.099998 v. 45.1¿¿
Looks like a rounding error? Is there some kind of precision setting somewhere?
Holy moley, I just searched for 11.2 odbc in the bug database. Check out Bug 9936269
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.
Thanks for writing this up so clearly. Working in government, we usually wind up with old software, so even though it is 2017, we all have 11.2.0.1 clients and this issue cropped up. Going to an even older version is an almost poetic solution to this problem for us.
Thanks for the post Charles. I found your solution after a few days of scouring the internet.
Can you please provide steps on how replaced sqora32.dll from oracle10g to oracle 11.2.0.1. I tried copy paste it from oracle 10g to oracle 11g and it gave me error while making ODBC connection.
Ashish,
You must make certain that if your 11.2.0.1 is the 32 bit version, that you copy that file from the 32 bit version of 10.2.0.1. Please note that this article is nearly 7.5 years old. You should NOT be using the 11.2.0.1 client at this point, but instead the 11.2.0.4 or a newer client version, which does not have the ODBC problem that is present in 11.2.0.1. Note that there are problems with the 11.2.0.3 client where at least one critical file is not registered using REGSVR32, but that is easy to fix if you must use 11.2.0.3.