?
Solved

mysqldump to oracle ddl

Posted on 2003-03-05
3
Medium Priority
?
1,487 Views
Last Modified: 2012-08-14
Hi,

I am using oracle 9.2 on linux.
I'd like to convert a "mysqldump" result
to a sql script (compatible with oracle).

Most of the incompatibilites are syntax details
that can be easily substituted by a sed script.
(number instead of float, '--' instead of '#" for
comments, and so on)

The main problem is that there are some auto_increment
fields in the mysql tables that should be converted
into oracle "sequence" + "trigger before insert".

For example here is the mysqldump output and the expected
oracle input script:

#
# mysql table creation
#
create table mydata ( id int auto_increment, val int );

#
# Oracle version
#
create sequence pkey_for_mydata
       increment by 1 start with 1 cache 2;

create table mydata ( id number, val number );

create TRIGGER trigger_primary_key BEFORE INSERT ON mydata
       REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
Begin
select pkey_for_mydata.nextval into :NEW.id from DUAL;
End;
/


#
# this insertion should work with both db
# with field 'id' being incremented automatically
#
insert into mydata (val) values ( 3 );

------------

I'd like to find a tool (command line interface if possible)
to do this conversion automatically. [ for example my2pg
do exactly this but to migrate to postgres ]

Does such tool exist ? (sqlldr ?)

Stephane

0
Comment
Question by:skian
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 9

Expert Comment

by:konektor
ID: 8072523
probably u can use some designer tool, like Oracle designer, Sybase DataArchitect, ... but that one of them, which can work with both types od databases : source where u have schema and Oracle - where u want to put schema. u'll use "backward engeering" - creating graphical data model from source DB and than use create srcript in oracle. i don't know it theese products can do it, u can checkt it.
0
 

Accepted Solution

by:
philcartmell earned 300 total points
ID: 8072526
I suspect this would require a bespoke converter. I'm not aware of a tool that does this.

The problem lies in things like sequences and auto_increment etc - however it would be a lot easier to write a mysql-->oracle converter as opposed to oracle-->mysql converter!! :)

If you would be interested in me quoting to do this let me know.

Cheers

Phil
0
 
LVL 1

Author Comment

by:skian
ID: 8426890
You're right, philcartmell, I ended up with writing my own tool


0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question