2012-03-20

[Oracle][Odbc]Numeric Value Out Of Range. (#0)

Sometimes it's easier to delete the entire statistics (dbms_stats.delete_table_stats) for the table affected :

 

Applies to:

Oracle ODBC Driver - Version: 11.1.0.6 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Symptoms

Attempting to link a table in Microsoft Access  may result in the following: 

Oracle's ODBC driver
ODBC -- Call failed.
[Oracle][ODBC]Numeric value out of range. (#0)

Microsoft ODBC driver for Oracle
ODBC -- Call failed.
[Microsoft][ODBC driver for Oracle]Error in column 11: Numeric value out of range (#0)


Examining an ODBC trace of the failure shows the problem occurs during a SQLGetData call for the 11th column of the results returned by a call to SQLStatisticsW

MSACCESS 310-934 EXIT SQLGetData with return code -1 (SQL_ERROR)
HSTMT 08F81A28
UWORD 11
SWORD 99 <SQL_C_DEFAULT>
PTR <unknown type>
SQLLEN 4
SQLLEN * 0x00136898

DIAG [22003] [Oracle][ODBC]Numeric value out of range. (0)

Changes

This behavior may occur with no noticeable changes to the environment.

Cause

The behavior occurs for very large tables (2.6 billion + rows),  and results when the value returned from the ODBC api call  exceeds the maximum value returnable via SQL_C_DEFAULT binding, and the error is expected behvior and indicates the value is too large to fit in the bind.

Solution

This is a limitation of Microsoft Access.

A potential workaround may be to use DBMS_STATS.SET_TABLE_STATS to alter the data dictionary statistics for the number of rows in the table so that the limit is not exceeded, but should not be undertaken without understanding the impact.  Consult with your DBA for further advice.

References

BUG:3319122 - CALLING SQLSTATISTICS ON VERY LARGE ANALYZED TABLE FAILS WITH MS ACCESS
 

Niciun comentariu:

Trimiteți un comentariu