Access97 Pass-Thru Query giving Numeric Value out of range(#0)

  • Thread starter Thread starter CLM
  • Start date Start date
C

CLM

I have a pass-thru query to oracle tables and it has been
running fine for months. Now all of a sudden I am getting
2 error messages. The first being ODBC - call failed and
then I get the Error in Column 15: Numeric Value out of
range(#0).

Is anyone familiar with this and what I can do about it?

CLM
 
CLM said:
I have a pass-thru query to oracle tables and it has been
running fine for months. Now all of a sudden I am getting
2 error messages. The first being ODBC - call failed and
then I get the Error in Column 15: Numeric Value out of
range(#0).

Is anyone familiar with this and what I can do about it?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Either (1) the column definition has changed to a smaller numeric type;
or (2) the values you are using now are greater than the values you were
previously using.

E.g.:

(1) The column was a Small Integer (I don't know what that is in Oracle
DBs), which has a range of -32,768 through 32,767. Someone changed the
column's data type to a TINYINT (Byte: range 0 to 255). Now it can't
accept values < 0 or > 255.

(2) The column was a TINYINT and you've been inserting values between 0
and 255. Now you've tried to add a value < 0 or > 255.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHrrBoechKqOuFEgEQKyfACeLwTsMDdQIbjBsxpp2/PDiPTKDY4AoI4x
HrdXABx9BZuR7hbVlPfDV50b
=yLty
-----END PGP SIGNATURE-----
 
If I were updating or appending to a table I would agree
that it is definitely an issue however this is just a
select query. In a select query there isn't a way to set
the datatype at least not that I know of.
 
Hi CLM,

From your description, I understand that the pass-thru SELECT query to oracle tables failed with the
following error message:

"ODBC - call failed" followed by:
[Microsoft][ODBC Driver for Oracle]Error in column #: Numeric value out of range.

Based on my experience, this is mapping error occurring due to the Oracle data type for the specified
column. The reason of this problem may be the ODBC driver issue or the data type of the column(s) in the
back-end Oracle database.

==================================

To isolate the problem, please check the following information:

1) Check the version of file msorcl32.dll (ODBC Driver for Oracle)

In the Oracle ODBC driver version 2.5, the values precision and scale are set to zero in such cases
according to the ODBC specifications. Hence, some applications that depend on these values usually bind
the columns in the result set to the lowest possible data type (Integer) for numeric data types. When the
values go out of range for these data columns, the Oracle driver throws the error message.

To resolve this issue, it is recommended that you upgrade your MDAC to 2.7 SP1 Refresh/ latest MDAC 2.8
version via the following link:
http://msdn.microsoft.com/data/downloads/updates/default.aspx#MDACDownloads

2) Check if the data type of the column(s) is changed on the Oracle Server.

If possible, set precision and scale on Oracle's NUMBER data type. For example: NUMBER(10,3).

3) Another possibility is that some data in the pass-thru query are resulting in numeric values that are larger
than what the driver or Access can handle.

In this case, we can try to modify the SQL statements such that they return commonly used data types. For
example: Select TO_CHAR(test_seq.NEXTVAL) from SYS.DUAL

For more information, you can refer to the following KB article:

199293 PRB: Numeric Value Out of Range Error with MS Oracle ODBC Driver Version
http://support.microsoft.com/?id=199293

====================================

CLM, lease apply my suggestions above and let me know if this helps resolve your problem. If the problem
persists after you tried all, please let us know your Access version and Jet40.dll file version. In this way, we
can have a chance to reproduce your problem for futuer analysis.

I'm looking forward to hearing from you.

Best regards,

Billy Yao
Microsoft Online Support
 
Back
Top