Problem: SQL Connection to Oracle Table Number Fields Have 6 Decimal Places Added

Solution or Workaround

When I connect to an Oracle table through SQL Connect, ArcView assigns 6 extra zero's after the decimal point. Why is this happening?

Answer:

You have most likely not set a SCALE to the number field. If no SCALE is set then ArcView will assign the extra 0's because the ODBC driver does not associate a precision value on the table. In Oracle, the PRECISION is equal to the total number of decimal digits (i.e. Width), the SCALE is equal to the
number precision (i.e. Decimal Spaces).

When you define a number field in Oracle there are two parameters which define
the Precision and Scale.

1) Creating the table in Oracle

SQL> CREATE TABLE NUM_TEST
2 (NUM_NUMBER, -Precision or Scale not defined
3 NUM_6_0 NUMBER (6,0), -Precision set to 6 and Scale to 0
4 NUM_6_3 NUMBER (6,3), -Precision set to 6 and Scale to 3
5* NUM_6 NUMBER (6) -Precision set to 6

2) Select Statement in Oracle

SQL> SELECT * from NUM_TEST;
NUM_ NUM_6_0 NUM_6_3 NUM_6

1 100000 100.123 100000
2 123 123.123 123
123 123 123 123
234.234 234 234.234 234

3) SQL connection in ArcView

NUM_ NUM_6_0 NUM_6_3 NUM_6

1.000000 100000 100.123 100000
2.000000 123 123.123 123
123.000000 123 123.000 123
234.234000 234 234.234 234

ArcView is assigning the extra zero's to any number field in which the SCALE
parameter is set to Zero.

4) Perform a DESCRIBE command in Oracle

SQL> descr NUM_TEST;
Name Null? Type
NUM_ NUMBER
NUM_6_0 NUMBER(6)
NUM_6_3 NUMBER(6,3)
NUM_6 NUMBER(6)

You will need to use the ALTER TABLE command to change the SCALE of the field so that you do not have the extra zero's. (i.e. Change the Scale for the Num_ field to (2)).

Article ID:000001541

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

SQL Connection to Oracle table number fields have 6 decimal places added

Solution or Workaround

Discover more on this topic