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 StepOn a linux box get your .mdb files together in a single folder
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
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
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.
Copy the .csv and .sql files to your windows box which has pl/sql developer.
Fire up pl/sql developer (6.0+)
Create tables in oracle using the .sql file mdbtools generated
Create sequences for each ID column. Set the cache number to 0 for the sequence.
Import the .csv data to oracle
Tools->text importer
Hit the icon in the top left to select the .csv file
Under ‘data to oracle’ tab select the owner, and table name
For any column names which were modified manually select that database field name
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.
For fields names that end in ‘date’ change the datatype to ’string’ and remove the todate function
The text importer picks ups some fields as number type when they should be string
Click import at bottom left
Update all your application sql code.
References and Resourcesan article by oracle.
Other ThoughtsAfter 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!
0 comments:
Post a Comment