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.








Follow

Get every new post delivered to your Inbox.

Join 144 other followers