Access to Oracle Migration

posted April 30th 2005 at 0345 EDT in All, Articles, ColdFusion, Oracle, Programming, SQL

Migrating from Access to Oracle is tough enough as it is, these are a few of my favorite things to make that go as smoothly as possible. Hopefully you will find this usefull in moving those databases, because I wish I had it when I started!

My assumption is of course that i’m the last one to be moving away from MS Access who has been using ColdFusion for a while. I’d be interested to hear if anyone else is moving away from it, and what to.

Tools I used:

mdbtools will allow you to dump the data in your access database. I had a problem with the rpm version, and needed to get the most recent revision from cvs to solve a few bugs in the app. http://mdbtools.sourceforge.net/

pl/sql developer A good sql program for creating tables and such, but version 6.0 really shines with it’s text importer component. http://www.allroundautomations.nl/plsqldev.html

Step by Step

  1. On a linux box get your .mdb files together in a single folder

  2. Create your oracle schemeas from the .mdb database (this shell statement just runs mdb-schema on each .mdb file in the working directory)

    for i in *.mdb;do mdb-schema $i oracle > $i.schema.sql;done

  3. Dump your data as csv (this shell statement just exports each table in each .mdb file in the working directory)

    for i in *.mdb;do for j in `mdb-tables $i`;do mdb-export $i $j>$i.$j.csv;done;done

  4. Customize any field changes

    Field and table changes are done In the .sql file generated from the access database structure. Remember to save your changes there! Things to watch out for:

    • I like to make sure each table has an ID column
    • memo fields in access export as clob(255). Normally these can end up as varchar2(4000) or smaller. 4000 was the size limit for varchar2 on my oracle server, i’m not sure if that is standard though. Obviously if you need more than 4k in size use a CLOB.

  5. Copy the .csv and .sql files to your windows box which has pl/sql developer.

  6. Fire up pl/sql developer (6.0+)
  7. Create tables in oracle using the .sql file mdbtools generated
  8. Create sequences for each ID column. Set the cache number to 0 for the sequence.
  9. Import the .csv data to oracle
    1. Tools->text importer
    2. Hit the icon in the top left to select the .csv file
    3. Under ‘data to oracle’ tab select the owner, and table name
    4. For any column names which were modified manually select that database field name
    5. For each date column select date for original data format, and then click ‘create sql’ to create the todate function in the import process. Modify the function depending on the data format.
    6. For fields names that end in ‘date’ change the datatype to ’string’ and remove the todate function
    7. The text importer picks ups some fields as number type when they should be string
    8. Click import at bottom left
  10. Update all your application sql code.

References and Resources

an article by oracle.

Other Thoughts

After this methodology was figured out, I heard word that you can export directly from microsoft access to oracle if you have an odbc connection setup to Oracle on your client computer (where you have access open). I have yet to verify this, but I sure I wish I had known this months ago. Either way you would still need to make any table changes and setup sequences to replace those autonumber fields.

As they say, the devil is in the details; so go root him out!

4 Responses

  1. #1 naveen
    4 years, 5 months ago

    i want to export data from oracle to csv format and then i have to export the csv formated data to excel sheet. how to do it? please sed me the code or explanation like how to do.

    thnks & rgds
    naveen

  2. #2 Geppe
    3 years, 10 months ago

    Use TOAD and export data tables directly in Excel format

  3. #3 Dario
    3 years, 3 months ago

    You can use oracle migration workbench.
    I’m using that tool to migrate structures and unixodbc, freetds and heterogeneous services to migrate data.

    I’m having troubles with clob, but I expect to solve them.

  4. #4 Martin
    8 months, 2 weeks ago

    Using ESF Database Migration Toolkit, it can migrate access to oracle or back in 3 steps. Also, it can migrate others database(SQL Server, MySQL, Excel, PostgreSQL…) to oracle and back.

    http://www.easyfrom.net/download/