Featured Database Articles

Oracle Migration Workbench - Part Two - Page 2

Getting data into a MySQL database

As an Oracle wizard, no
doubt you are intimately familiar with how to create tables and insert data.
There is very little difference when using MySQL. Three of the biggest
differences are:

To make a MySQL table more
like Oracle, specify the type of table at the end of a create table statement.
The type to specify is "InnoDB" and is specified with a "type=InnoDB"
clause at the end of the create table statement.

Your choice of datatypes is
quite similar, but note that Oracle's VARCHAR2 is MySQL's VARCHAR, and that
number datatypes are slightly different. If you just want a whole number, use "int"
and for decimal type numbers, use decimal(L,P) where L is the length and P is
the precision.

MySQL's date format may
cause a problem for you as it uses a YYYY-MM-DD format. In a way, that is
actually a lot more convenient as there is no doubt as to whether or not you
are using day/month or month/day.

If you like SQL*Loader, you
will like MySQL's data loading and outputting via an INFILE and OUTFILE. Even
before getting to Oracle Migration Workbench, you have the means to transfer
data, even if it is just one table at a time.

I have taken the Scott
schema and arranged it into a MySQL suitable format. The root user will create
the tables and perform the "load
data infile" to populate the "msemp" table. The example
uses the same table names but with an "ms" placed in front (msemp, msdept,
and so on). You can choose how the data is delimited in the infile (comma
separated or tab delimited, for example). I used tab delimited to make the data
easier to read (plus that is the default), but the CSV version works just as
well.

What follows are the
commands to create the tables and use the infile to load the msemp table.
Create a data infile and reference where shown below, using the backslash to
escape the Windows directory path backslash separator.

This should remind you of
how you can do the same thing using Oracle's export utility. This concludes the
setup phase of MySQL. Take note of the fact that Migration Workbench connects
to your MySQL database as the user "root." In a way, the MySQL user
named tiger wasn't necessary, but creating him parallels the privileged user
versus schema owner concept in Oracle.

Before leaving MySQL, issue
a "\s" at the MySQL prompt and note the port number shown (TCP port of
3306 shown below). Migration Workbench will use that port number to establish a
connection between your Oracle and MySQL databases.