mysql to oracle

Question

I've googled this but can't get a straight answer. I have a mysql database that I want to import in to oracle. Can I just use the mysql dump?

Answer 1

The answer depends on which MySQL features you use. If you don't use stored procedures, triggers, views etc, chances are you will be able to use the MySQL export without major problems.

Take a look at:

mysqldump --compatible=oracle

If you do use these features, you might want to try an automatic converter (Google offers some).

In every case, some knowledge of both syntaxes is required to be able to debug problems (there almost certainly will be some). Also remember to test everything thoroughly.

Answer 2

If the dump is a SQL script, you will need to do a lot of copy & replace to make that script work on Oracle.

Things that come to my mind

• remove the dreaded backticks
• remove all ENGINE=.... options
• remove all DEFAULT CHARSET=xxx options
• remove all UNSIGNED options
• convert all DATETIME types to DATE
• replace BOOLEAN columns with e.g. integer or a CHAR(1) (Oracle does not support boolean)
• convert all int(x), smallint, tinyint data types to simply integer
• convert all mediumtext, longtext data types to CLOB
• convert all VARCHAR columns that are defined with more than 4000 bytes to CLOB
• remove all SET ... commands
• remove all USE commands
• remove all ON UPDATE options for columns
• rewrite all triggers
• rewrite all procedures

Answer 3

Nope. You need to use some ETL (Export, Transform, Load) tool.
Oracle SQL Developer has inbuilt feature for migrating MySQL DB to Oracle.
Try this link - http://forums.oracle.com/forums/thread.jspa?threadID=875987&tstart=0 This is for migrating MySQL to Oracle.