Migration to MySQL from Oracle

Thus far, the road to move from Oracle to MySQL has been a little rocky. We got access to our new MySQL DB about a week ago, and there's a meeting next week with the all the DBAs to discuss administrative issues like access permissions, backup schedules, etc.

Along with that, the DBAs just set up a dblink for us from our eCAFE and ODS Oracle dbs to our MySQL db. The links are supposed to make it easier for us to move the data from one to the other.

In the case of the Oracle eCAFE db, this one holds all the data from our past semesters, which of course needs to be transferred into the new system. The catch is that the db schema has changed dramatically, so it isn't an issue of just transferring data, but translating it to the new schema.

The ODS db is where we get all our data for the upcoming semester, class schedules, enrollment, teaching assignments, etc. Of course, their views don't match our schema in the slightest, so we need to do more translating of data in this case as well.

Until we got the links, I was getting the data by any of the following methods:
1.) Perl scripts that connected to one db, did the translations, and then inserted into the MySQL db. The problem here is that getting perl DBI installed on Max OS X Leopard was a royal pain, and not one I can ask my fellow developers to go through when their plates are already full.
2.) Doing a query in the appropriate Oracle db, exporting the results, running sed commands on the sql file to make changes as needed, and then importing the modified dump file. Works, but takes time, and is rather error prone.

So we got the dblinks and there was much celebrating... At least until I tried to use them in a real-world example. I guess I needed to do more research, but I had visions of just running queries in my oracle ecafe db (ecafeproduction) like

insert into ecafe.person@mysql-remote (username) select username from ecafeproduction.person

This failed on so many levels.
1.) The syntax only works if both databases involved are Oracle. Since one is MySQL, I got "ORA-02025: all tables in the SQL statement must be at the remote database.
Cause: The user's SQL statement references tables from multiple databases." To fix this, I had to run the query in SQL*Plus and change the query format:
copy
from <username>/<password>@<db>
append <username>@mysql-remote("username")
using select username
from ecafeproduction.person;

2.) Once I changed to sqlplus and the copy command, I discovered that any insert or append required that I provide values for ALL the columns in the destination table. Since the tables don't match up, this is a problem. To top it off, I have auto-generated id fields in the destination table which I definitely could not provide values for. The solution to this is a hack, I create a table with the subset of values that I am retrieving from the original db, and then insert into this intermediary table. Then I go into MySQL, and write another insert to copy from the intermediary to the real destination table. Not fun, but it works.

So, thus far the process has been a bit of a trial, but hopefully we'll work all these issues out. At least the transfer from the original eCAFE db to the new one is a one-time deal. I can focus on automating the ODS process later when the development cycle has calmed down.