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 @tableList =<FHI>;
close(FHI);
my $pid;
print "starting at: ";
system("date");
foreach my $table (@tableList)
{
$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/[EMAIL PROTECTED]
