Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

mysqldump to oracle ddl

Posted on 2003-03-05
3
Medium Priority
?
1,498 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
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

572 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