PRB: Numeric Field Displays #Deleted with Oracle Linked Tables

#Deleted entries are seen when Oracle linked tables containing number datatypes are opened using either Access 97 SR2 or Access 2000. This behavior is seen with both Oracle 7.3.4 server as well as Oracle 8.0.5 server irrespective of the version of the MSORCL32.DLL file used to create the DSN.

In Oracle, the NUMBER datatype by default has a precision of 38; that is it, is equivalent to NUMBER(38). This precision is much greater than what the Microsoft Jet database engine 3x/4x can handle. Thus, in this scenario Jet has to perform lookups using data that it cannot handle. The error is seen because Jet cannot succeed in the lookup and it therefore assumes that the data it's trying to find has been deleted.

The only workaround for this problem is to create the table using numbers of lower precision than the Oracle default precision of 38. With Jet 4.x, a precision of 28 is allowed.

This behavior is by design. Microsoft has confirmed this to a design limitation of the Jet database engine.

This problem is seen on account of the inability of Jet to handle the large precision of Oracle's NUMBER datatype. This is not a driver issue because no problem is encountered when using pass-through queries or any other non-Jet based ODBC application.

Steps to Reproduce Behavior

NOTE: Use SQL *PLUS to run the following SQL Statements.

• Case 1: Problematic table creation

  Create table MyNumTest1 (col1 number not null primary key, col2 varchar(10));
  
  insert into MyNumTest1 values (1.234, 'MyTest');
  insert into MyNumTest1 values (10.234, 'MyTest');
  
  						

• Case 2: Using workaround syntax

  Create table MyNumTest2 (col1 number(16,3) not null primary key, col2 varchar(10));
  
  insert into MyNumTest1 values (1.234, 'MyTest');
  insert into MyNumTest1 values (10.234, 'MyTest');
  
  						

Once this is done, create an ODBC DSN (preferably a SYSTEM DSN) using the Microsoft ODBC for Oracle (version 2.573.4202) driver to connect to the Oracle server on which the above mentioned tables were created. Link these tables from Access 97 SR2 and Access 2000 using this DSN. #Delete entries are seen when opening the table MyNumTest1. No such problems are encountered with the other table.

The table definition has to be changed to get around this limitation of Jet 3x/4x with numbers of large precisions.