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
