By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,719 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,719 IT Pros & Developers. It's quick & easy.

Databasetransfer between oracle 8 database and MySQL database

P: n/a
Is it somebody out there who has made a migration from an Oracle server to an MySQL server??

The scenario is as simply:
I've got a Oracle 8 server with a database with content that I want to transfer to
a MySQL database. No special data, constraints etc that MySQL not will handle.

My solution is to reverse engineer the database from ERStudio and then produce a SQL script that
will insert the data into the MySQL engine.

But I can't do this, because the customer want me to do a PL/SQL script that does this, even if he
never have worked with Oracle.. So the question is.. Do someone know a simplier way to do this
or do I have to teach myself PL/SQL and make a script that produces a
MySQL sql script ?

Regards,

Patrick
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Patrick wrote:
I've got a Oracle 8 server with a database with content that
I want to transfer to > a MySQL database. No special data,
constraints etc that MySQL not will handle.
My solution is to reverse engineer the database from ERStudio
and then produce a SQL script that will insert the data into
the MySQL engine.
But I can't do this, because the customer want me to do a
PL/SQL script that does this, even if he never have worked
with Oracle.. So the question is.. Do someone know a simplier
way to do this or do I have to teach myself PL/SQL and make a
script that produces a MySQL sql script ?


Default import/export methods in Oracle and MySQL are the first choice
for this kind of job. This shouldn't normally be a problem in your
scenario.

If your customer insists on a perl solution, then 99% chances you will
want the DBI module. It supports a wide range of databases of which
Oracle and MySQL (http://dbi.perl.org).

Here are some perl examples that should get you started:

# query to Oracle that ties result to variables
use DBI;
$dbh = DBI->connect("dbi:Oracle:Oracle_DBName",
"Oracle_user","Oracle_pass");
$sth = $dbh->prepare("SELECT id, name, title, phone FROM employees");
$sth->execute();
($id, $name, $title, $phone);
$sth->bind_columns( undef, \$id, \$name, \$title, \$phone );
while( $sth->fetch() )
{ print "$name, $title, $phone\n"; }
$sth->finish();
$dbh->disconnect();

# query to MySQL that ties result to variables
use DBI;
$db = DBI->connect("dbi:mysql:MySQL_DBName:localhost",
"MySQL_user", "MySQL_pass");
$query = $db->prepare("SELECT id, name, title, phone FROM employees");
$query->execute;
$numrows = $query->rows;
while (@array = $query->fetchrow_array)
{
($id, $name, $title, $phone) = @array;
print "$name, $title, $phone\n";
}
$query->finish;
$db->disconnect;

# just execute query in Oracle
use DBI;
$dbh = DBI->connect("dbi:Oracle:Oracle_DBName",
"Oracle_user","Oracle_pass");
$dbh->do("INSERT INTO employees VALUES ('1','Jeff','Mr','12345')");
$dbh->disconnect();

# just execute query in MySQL
use DBI;
$db = DBI->connect("dbi:mysql:MySQL_DBName:localhost",
"MySQL_user", "MySQL_pass");
$query = $db->prepare("INSERT INTO employees VALUES
('1','Jeff','Mr','12345') ");
$query->execute;
$query->finish;
$db->disconnect;

--------------
Hope this helps

Bart Van der Donck
P.S. CommerciŽle oplossing ook beschikbaar :-) zie dotinternet.be
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.