Option A - Access Export.
Pretty simple strategy here.
- Open the Access file.
- Go to the Tables section.
- Double click on the table to open it.
- Right click on the table, then choose Export > Text File.
- In the dialog box, choose a file name. Make sure the 3 boxes are all unchecked. Click OK.
- Choose Delimited, then click Next.
- Choose Comma delimited, with a Text Qualifier of a double quote.
- Click on Next, then Finish.
- In Oracle, create a table that matches the Access one.
- Create the control file for Sql Loader.
- Now use Sql Loader to load the data into your new Oracle table.
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.
- Use SqlDeveloper 3, not the newest 4. 4 does not seem to map any data types, so it fails miserably.
- Connect to the target instance.
- Right click, Migration Repository, then Associate Migration Repository.
- Tools, Migration, Microsoft Access Exporter, Run Microsoft Access 2007 Exporter....
- 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.
- 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.
- 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.
- 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.
- 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.
- Now, use Sql Loader to load the data into your newly created table.