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).

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).

Automatically applying the TO_CHAR function

To circumvent this this problem, select Option > Technical Parameters > For Oracle, use TO_CHAR with NUMERIC cols defined with no precision/scale.

When this option is specified, AQT will amend the default query for the table to use TO_CHAR with columns. So instead of using:

select * from table

AQT will display the table using 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.

Advanced Query Tool
© 2023 Cardett Associates Ltd. All rights reserved.