Linked Server Numeric Error on Oracle 10g/11g – Why It Happens and How Updating the Oracle Client Fixes It

Server A runs SQL Server 2005 and an Oracle 10g client (10.2.0.1); Server B runs Oracle 11g (11.2.0.1). A linked server is created on Server A to access Oracle data on Server B.

When the Microsoft OLE DB Provider for Oracle (MSDAORA) is used, queries succeed but performance is poor. Switching to Oracle's OLE DB Provider (OraOLEDB.Oracle) introduces error 9803: "Data is not compatible with type 'numeric'." The error occurs for any SELECT against the linked table LINKDB..USER1.T1 or via openquery:

select * from LINKDB..USER1.T1;
select * from openquery(LINKDB,'SELECT * FROM T1');

The problematic column F1 is defined as number(19,4). Extensive testing with different numeric values revealed a pattern:

5.0000 – works

9.0000 – works

10.0000 – fails

20.0000 – fails

50.0000 – fails

50.1000 – works

51.0000 – works

100.0000 – fails

110.0000 – works

100.0100 – works

500.0000 – fails

1000.0000 – fails

1200.0000 – fails

1201.0000 – works

The observed rule is that numbers ≥10 with only one non‑zero digit (or the first two digits for numbers ≥1000) trigger the error.

Checking the SQL Server version with select @@version shows SQL Server 2005 without Service Pack 4. After applying SP4 the problem persists.

select @@version;

The breakthrough comes from updating the Oracle client on Server A to the Oracle 11g client. After installing the newer client, the linked server queries execute without error.