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:

• if possible, define the columns with fixed precision. Eg. NUMERIC(28,2) rather than NUMERIC.
• use the Microsoft ODBC Driver for Oracle. Normally we don't recommend using this driver, however this driver does process NUMERIC columns without loss of precision.
• display the column using a column function. Examples:
  • TO_CHAR(numeric_column)
  • CAST(numeric_column AS NUMERIC(30,3))

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.