| Mailing List | | Home | | MySQL General - General MySQL discussion | | MaxDB - Everything about MaxDB, formerly known as SAP DB | | MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP | | MySQL++ - Programming with the C++ API to MySQL | | ODBC - ODBC with the MySQL Connector/ODBC driver | | Java Help - Mostly related to the MySQL Connector/J driver | | Perl - Perl support for MySQL with DBI and DBD::mysql | | GUI - MySQL GUI Tools | | Announcement |
|
|
 | | | a little perl script to export data from oracle to maxdb | a little perl script to export data from oracle to maxdb 2004-05-10 - By Filip Sergeys
Back
Hello,
In case someone has the pleasant task of moving data from oracle to
maxdb, maybe this little perl script below, with some custom tweaking,
can help.
It generates 2 files:
- A CSV data file
- A control file containing DATALOAD command for use with loadercli
Don't just save it in a file and run it. Read it through so you
understand how it works. It is not bullet proof and does not handle
every possible exception.
I just wanted to give something back as a thanks for al the people on
the mailing list that have helped so far.
The working is explained in the script itself.
Maybe some nice extentions would be:
- Generating an extra file with a create table statement for maxdb.
- Have the script feed the loadercli directly. (But therefor I need to
get perl to work with the loader, which I haven't succeeded to do yet)
- Make it a bit more interactive
- ... (if you have idea's)
Cheers,
Filip
#!/usr/bin/perl
#=====================================
# Author: Filip Sergeys
#
# What does this do?
# Short: export data from oracle in a format that is easily loaded into
maxdb.
# Long: It connects to an oracle instance, dumps the data for every
table you specify in a separate file (.data)
# and creates a control file (.ctl)that you can use with loadercli to
load it into maxdb.
# loadercli -d <dbname> -u <user>,<password> -b <tablename>.ctl .
# Thus the tables need to be create on beforehand in maxdb.
#
# How to use it?
# You need DBD::Oracle
# ./oracle2maxdb <oracle-user> <oracle-passwd> <oracle-instance>
<tablelist>
# where:
# <oracle-user> is the username you want to connect to oracle with.
# <oracle-passwd> is the password for the oracle user.
# <oracle-instance> is the oracle database instance you want to connect
# <tablelist> is a file with all the tablenames you want to export.
Every tablename on a separate line.
# To speed up things, a separate process is started for every table.
#
# Do whatever you want with the script, I'am not responsible for
possible caused data -damage or -loss. Take backups.
# If you do improvements or extentions, I will be happy if you sent me a
copy.
#=======================================
use warnings;
use strict;
use DBI qw (:sql_types);
my $ora_user = $ARGV[0];
my $ora_passwd = $ARGV[1];
my $ora_instance = $ARGV[2];
my $table_list = $ARGV[3];
my %attr = (
RaiseError => 0,
PrintError => 1,
AutoCommit => 1,
ChopBlanks => 1,
ShowErrorStatement => 1,
LongReadLen => 8000,
LongTruncOk => 1,
odbc_default_bind_type => 0
);
open (FHI, "<", "$table_list") or die "couldn't open file for reading:
$!\n";
my @(protected) =<FHI>;
close(FHI);
my $pid;
print "starting at: ";
system("date");
foreach my $table (@(protected))
{
$table =~ s/\s//g;
$pid = fork();
if($pid == 0){
my $oracle =
DBI->connect("DBI:Oracle:$ora_instance","$ora_user","$ora_passwd",
\%attr)
or die "Couldn't connect to database: " . DBI->errstr;
my $colNames = [];
my $colName;
my $colStr;
my $columns;
my $qry;
my $rows = []; # Cache for batches of rows
my $slice = [];
# $slice is an array reference, fetchall_arrayref uses
"fetchrow_arrayref"
# to fetch each row as an array ref.
my $row;
my $rc;
my $ora_sth;
my $line;
my $tableOutput = "$table" . ".data";
my $ctrlOutput = "$table" . ".ctl";
my $ctrlCmd;
my $colNum = 0;
open (FHO, ">", "$tableOutput") or die "couldn't open $tableOutput for
writing: $!\n";
open (CHO, ">", "$ctrlOutput") or die "couldn't open $ctrlOutput for
writing: $!\n";
$qry = "SELECT * FROM $table";
print "$qry \n";
$ora_sth = $oracle->prepare("$qry")
or die "Couldn't prepare statement: " . $oracle->errstr;
$ora_sth->execute
or die "Couldn't execute statement: " . $ora_sth->errstr;
$colNames = $ora_sth->{NAME_lc};
foreach $colName (@$colNames){
$colNum++;
$colName = uc($colName);
$colStr .= "\"$colName\" $colNum\n";
}
$ctrlCmd = "DATALOAD TABLE $table\n $colStr infile '$tableOutput'
separator '|' delimiter '\\'' date ISO time ISO timestamp ISO";
print CHO "$ctrlCmd";
close (CHO);
INS: while($row = shift(@$rows) ||
shift(@{$rows=$ora_sth->fetchall_arrayref($slice,1000)}) || "0") {
foreach my $col (@$row) {
$line .= "'$col'|";
}
$line = substr($line,0,length($line)-1);
print FHO "$line \n";
$line = '';
}
close(FHO);
$ora_sth->finish;
$oracle->disconnect;
print "finished at:";
system("date");
print "\n\n";
exit;
}
} #END
--
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* System Engineer, Verzekeringen NV *
* www.verzekeringen.be *
* Oostkaai 23 B-2170 Merksem *
* 03/6416673 - 0477/340942 *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=mysql@(protected)
|
|
|