11.2.0.1 ODBC Update Problem

2 11 2010

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.


Actions

Information

5 responses

2 11 2010
joel garry

45.099998 v. 45.1¿¿

Looks like a rounding error? Is there some kind of precision setting somewhere?

2 11 2010
joel garry

Holy moley, I just searched for 11.2 odbc in the bug database. Check out Bug 9936269

2 11 2010
Charles Hooper

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.

2 11 2010
Charles Hooper

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.)

4 11 2010
joel garry

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 141 other followers

%d bloggers like this: