Entry

How can I convert a MySQL database to Oracle?

What problems might I face converting between Oracle and MySQL?

Jan 24th, 2000 19:28
Nathan Wallace, Phillip Ross

Your MySQL stuff should run fine on Oracle - there may be some minor
differences but on the whole MySQL is a subset of Oracle SQL. (you
should be able to move stuff across using mysqldump).

The ease at which code ports from MySQL to Oracle is pretty much
proportional to your expertise with both.  Some rough spots are dealing
with auto increment and timestamp fields.  While MySQL uses an auto
incrementing field and does all of it's work just by defining the field
as an auto increment type, with Oracle you must create a sequence and
either create a trigger on the table to read the next value from the
sequence and place it in the field that you want to autoincrement, or
you put the sequence directly in your insert statements.  It's pretty
much the same with timestamp type.  In Oracle you would create a trigger
on the table to put the sysdate in the field you want to stamp, or you
would specify sysdate in your insert or update statements.  It's not as
simple as defining a field type, but it offers a lot more flexibility,
so you can consider it both an advantage and disadvantage.

Another rough spot is that MySQL's API has a function to return the ID
of a row that you just inserted.  PHP has a function that directly maps
to this MySQL function,a nd when you want to get the id of a row you
just inserted, you make a call to that PHP function.  In Oracle, you
need to define a placeholder in your insert statement and bind it to a
PHP variable.  You use a RETURNING clause in your insert statement and
you can return the ID and whatever other values from the insert
statement that you want.  The PHP variable that you defined and bound to
the placeholder will then contain the return values.  Again not as
simple as a function call but it offers a lot more flexibility.

Aside from those rough spots converting code from MySQL to Oracle is
relatively easy.


The reverse is a lot more complicated as MySQL doesn't do everything
Oracle does. Stuff to watch out for particularly are VIEWS, stored
procedures and sub-selects (a SELECT/UPDATE/DELETE statement containg a
SELECT statement).

Here's a partial list of diffs that may lead to problems when converting
from Oracle to MySQL...

MySQL does not have commit/rollback, or session transaction logic
MySQL does not have archive log mode
MySQL does not have hot-backups, read/write backups, like with rman
MySQL does not have sub-selects
MySQL does not have sequences, its autoincrements are better
MySQL does not have row level locking, do it with careful GET_LOCK()s
MySQL does not have triggers or procedures, use your app logic for these
...
MySQL does not need 200M  to install
MySQL does not need a $1000 yearly support contract for updates