Problem solve Get help with specific problems with your technologies, process and projects.

Importing data from Access to Oracle

How can I import data from MS Access database to Oracle database? This is a one time job. Is there any tool provided by TOAD, or do I have to write some code to connect these two databases and port data from one to other? If yes, then what should be the code?
You have a few common options at your disposal.

One of the most common ways to perform this task is to save the MS Access tables in a flat file. You can do this by selecting the table and then choosing File->Export. In the Save As Type box, choose the Text File option. Then follow the wizard to save the data to a flat file. Once the data is in a flat file, you can use Oracle's SQL*Loader utility to load the data into an Oracle table.

Another common method is to create an ODBC DSN for your Oracle database on your workstation. You will need Oracle client installed so that you can use Oracle's ODBC drivers. Then select the table you want to load into Oracle and choose File->Export. In the Save As Type box, choose ODBC Datasources. You will then be prompted for your DSN you created above. Oracle will then create the table and move the data for you.

Finally, you can use Oracle's Migration WorkBench (OMWB) to move the data for you. You can get a copy of this utility on Technet (http://technet.oracle.com).

Dig Deeper on Oracle database design and architecture