You lost decimals when using Oracle 10g XE, ODBC, Visual FoxPro driver
Scenario
------------
W2K SP4 Server, disks C: = operating system, E: = data
Oracle 10g XE, home, E:\ORACLE
Legacy data in E:\LEGACY, many .DBF files
ODBC DSN, using Visual FoxPro Driver, to .DBF files contained in E:\LEGACY, named LEGACY
Oracle 10g XE configured OK...
*.ORA, LISTENER, TNSNAMES, initLEGACY.ora, to access ODBC
DATABASE LINK 'LEGACY' to access ODBC DSN 'LEGACY'
Test
------
SELECT * FROM <one_table_at_E:\LEGACY>@LEGACY runs ok, data is showed
SELECT <numeric_field> FROM <one_table_at_E:\LEGACY>@LEGACY runs ok, data is showed aswell
but <numeric_field> losts its decimal places, example: 1234.56 is showed as 1234.
Solution
-----------
SELECT <numeric_field>*100 FROM..., runs ok and field does not lost its decimal places.
Problem
-----------
When created a VIEWS to LEGACY DATA...
CREATE VIEW LEGACY_01 AS SELECT <numeric_1>*100 FROM dbf1@LEGACY;
CREATE VIEW LEGACY_02 AS SELECT <numeric_1>*100 FROM dbf2@LEGACY;
CREATE VIEW LEGACY_03 AS SELECT <numeric_1>*100 FROM dbf3@LEGACY;
and then a summary view
CREATE VIEW LEGACY_ALL AS
SELECT * FROM LEGACY_01 UNION
SELECT * FROM LEGACY_02 UNION
SELECT * FROM LEGACY_03
shows <numeric_1> field with its decimal places lost, but
if you select only from one view,
CREATE VIEW LEGACY_ALL AS
SELECT * FROM LEGACY_01
you still has <numeric_1> field with its decimal places, then
you do not lost decimals when using view over view, i think so.
Question
------------
Is there any solution to access legacy data in FoxPro .DBF files
not loosing decimals?
Is anyone experimenting this behaviour?