Migrating from Access to Oracle

Two Options.  One provides a lot more detail if you need it (constraints, indexes, ddl for tables, control files for Sql Loader, etc.).  The other simply creates a delimited text file that can then be imported using SQL Loader.

Option A - Access Export.
Pretty simple strategy here.

1. Open the Access file.
2. Go to the Tables section.
3. Double click on the table to open it.
4. Right click on the table, then choose Export > Text File.
5. In the dialog box, choose a file name.  Make sure the 3 boxes are all unchecked.  Click OK.
6. Choose Delimited, then click Next.
7. Choose Comma delimited, with a Text Qualifier of a double quote.
8. Click on Next, then Finish.
9. In Oracle, create a table that matches the Access one. 
10. Create the control file for Sql Loader.
11. Now use Sql Loader to load the data into your new Oracle table.

Option B - Sql Loader.
This did not work as advertised for me.  Here are the steps I took to finally be able to migrate a single table from Access to Oracle, using the Sql Developer utility to do this.

1. Use SqlDeveloper 3, not the newest 4.  4 does not seem to map any data types, so it fails miserably.
2. Connect to the target instance.
3. Right click, Migration Repository, then Associate Migration Repository.
4. Tools, Migration, Microsoft Access Exporter, Run Microsoft Access 2007 Exporter....
5. This should bring up Access.  Pretty simple.  Pick the mdb to migrate, make sure the "migrate data" is checked.  I used c:\temp for the output directory.
6. Now, back in SqlDeveloper, Tools, Migration, Migrate.  Pick your target connection, give it a name, choose an output directory.  Whenever you have the choice, pick "Offline"!  This is key, as we are going to hack some files when we are done.
7. Once the migration finishes, it should bring up the script in a worksheet.  Just find the part with the create table ddl, and run that to create your target table.
8. Now that you have an empty table, shell out to a command prompt and drill down until you find a control file for Sql Loader.  Put in the correct location of the data file.  For me, I put this all under c:\temp, so after drilling around a bit it wasn't too hard to locate the data and ctl files.
9. Also, check your date formats.  For me, it was not really close.  The dates were in a mm/dd/yyyy hh24:mi:ss format, but the control file had them masked completely wrong.
10. Now, use Sql Loader to load the data into your newly created table.

Ugly, but it did finally work.

Mike