Previous Topic

Next Topic

Book Contents

Book Index

Display of large Oracle numeric columns is incorrect

With Oracle there is a problem with columns defined as NUMERIC (without a precision or scale specified).

Because the precision / scale is unknown, the Oracle ODBC Driver decides to handle these columns in the same way as it handles floating-point columns (eg. those defined as FLOAT). However, as a result, there is a loss of precision for values of more than 16 digits in length. You will notice this in AQT as large values will have their last digits replaced with zeros.

There are a number of ways to circumvention this problem:

Note that these comments also apply to DECIMAL columns (which are largely similar to NUMERIC columns).

Configuring AQT to use a column function

It is possible to configure AQT to always display NUMERIC columns using one of these functions. In the config file for Oracle (oracle.cfg) place a line such as the following at the bottom:

displaycol;NUMBER;TO_CHAR(:);

or

displaycol;NUMBER;CAST(: AS NUMBER(30,2));

When either of these two specifications are included in the cfg file, the function will be applied to all NUMERIC columns in your table when the table is displayed.

How does this work? When AQT displays a table it does this by using SQL such as:

select * from table

however when a displaycol specification is used, AQT will instead use SQL such as:

select col1, col2, col3, TO_CHAR(numeric_col) as numeric_col from table

If you click on a table (in the Database Explorer window) and go to the SQL window, you will see this SQL.