Q

Importing FoxPro and Access databases into Oracle

This Content Component encountered an error

How can we import FoxPro and Microsoft Access databases into Oracle? And how can we export an Oracle table to Foxpro and Microsoft Access databases?

The key to both of your scenarios lies in using an intermediary format. With FoxPro, you should be able to export a table's contents to a comma delimited file (or some other delimiter). To export from MS Access to a comma delimited file, select the table, then choose File->Export. Once you have it in this database-independent format, you can then load it into Oracle. Oracle's tool for loading text files into Oracle tables is SQL*Loader.

To go the other way, you need to get the data out of Oracle in comma delimited format. You can use the DBMS_UTILITY.TABLE_TO_COMMA supplied package for this exact task. Or, you can write a SQL statement similar to the following:

SELECT empno||',"'||ename||'",'||deptno FROM emp;

In the above SQL statement, I use the concatenation operator to make the output from multiple columns appear as one field. I also enclosed the ENAME value in double quotes. The SPOOL command can be used to write the output to a file.

Once you have the comma delimited file, you will need to use your database's utilities to load it into a table. I'm not sure how you do this in FoxPro. In MS Access, choose File->Get External Data->Import. Then select your file. A wizard will run you through the import process.

Another intermediary format to use is a program like Perl that can pull from one data source and push into another. Perl accomplishes this by connecting to both data sources through ODBC connections. MS Access can utilize an ODBC connection to an Oracle database without any middle man.


This was first published in September 2004

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close