Oracle data being converted in Access
vinhnguMar 13 2006 — edited Apr 6 2006Hi All,
I am using PL/SQL Developer version 6.0.3.893 (Oracle 9i 9.2) and Access 2002. I need to export data from Oracle and import into Access in order to make some reports. But I ran into the suituation that data had been converted after exporting result in CSV or TSV. Even though I activate the option "Number fields to_char" in Preference, but have no luck.
Here what I did:
After excuting a query in PL/SQL Developer, one field of the return result contains ID with 17 digits. The ID looks like this '40000000000324003'. I want to export the result in CSV or TSV, and import into Access, but the ID converted to number and it look like this 4.0000000000324E+16. Is there a way to preserve the ID with 17 digits after exporting to CSV, TSV?
I have tried other ways, but still happens the same thing:
1- Using ODBC connection from Access to Oracle to link tables
2- Changing datatypes in Access to TEXT with field size 255
NOTE: The only way that I can preserve the datatype of ID is that after having results in PL/SQL, I "Fetch last Page", and right click on result pane, choose "Copy to Excel ..." and then import the excel file into Access. I can not use this way is because number of row return more than 1 million and PL/SQL Developer does not allow to fetch that many rows.
Is the a way to acomplish the task? Please advice.
Thanks,
VN