Maybe this example will help you,
in this example I am doing a copy of a table from Oracle to Mysql taking as a parameter just the name of the table:
#!/usr/bin/perl -w
use strict;
use DBI;
use Date::Calc qw(:all);
use Switch;
use Time::HiRes qw/ time gettimeofday tv_interval /;
$| = 1;
my @Month_name = ( "JAN", "FEB", "MAR", "APR", "MAY", "JUN",
"JUL", "AUG", "SET", "OCT", "NOV", "DEC" );
sub tstampms {
my $u_sec = time;
my @date = localtime($u_sec);
my $format = sprintf(
"%4d-%s-%02d %02d:%02d:%02d.%06d",
$date[5] + 1900,
$Month_name[$date[4]],
$date[3],
$date[2],
$date[1],
$date[0],
substr($u_sec, index($u_sec, '.') + 1)
);
print $format, "\n";}
#defalt name for the table
my $hbn = 'trades';
$hbn = $ARGV[0] if $#ARGV > -1;
# mysql database information
my $db="db";
my $host="localhost";
my $userid="user";
my $passwd="password";
my $connectionInfo="dbi:mysql:$db;$host";
# make connection to database
my $dbh = DBI->connect($connectionInfo,$userid,$passwd)
|| die( $DBI::errstr . "\n" );
# database information to connect to ORACLE
my $port1='1521';
my $host1="192.168.133.127";
my $sid1="oradb1";
my $userid1="user";
my $passwd1="password";
#my $connectionInfo1="dbi:Oracle:HOST=$host1;SERVICE_NAME=$service1;PO
+RT=$port1";
my $connectionInfo1="dbi:Oracle:HOST=$host1;SID=$sid1;PORT=$port1";
# connect to the Oracle server
my $db1 = DBI->connect( $connectionInfo1, $userid1, $passwd1 )
|| die( $DBI::errstr . "\n" );
$db1->{AutoCommit} = 0;
$db1->{RaiseError} = 1;
$db1->{ora_check_sql} = 0;
$db1->{RowCacheSize} = 16;
# primary key
my $SEL2 = qq[SELECT cols.column_name
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = ?
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner];
my $sth1 = $db1->prepare($SEL2);
$sth1->execute(uc($hbn));
my @row = $sth1->fetchrow_array();
my $PKeyn = $row[0];
print "Key field $PKeyn\n";
# get fields info trasformed to mysql format
my $SEL3 = qq[select column_name,
( CASE data_type
When 'VARCHAR2' THEN 'VARCHAR'||'('||data_length||')'
When 'DATE' THEN 'DATE'
When 'NUMBER' THEN 'DOUBLE'||
( CASE nvl(data_precision,-1)
when -1 then null
else '('||data_precision||','||data_scale||')'
end ) End) data_typ,
decode(NULLABLE,'N','NOT NULL') nl
from ALL_TAB_COLUMNS
where table_name = ?
order by table_name,column_id];
# excute SQL
my $sth2 = $db1->prepare($SEL3);
$sth2->execute(uc($hbn));
#drop table in mysql db schema
my $query = "DROP TABLE IF EXISTS $hbn";
print "$query\n";
my $sth = $dbh->prepare($query);
$sth->execute();
#construct create sql statement for Mysql
$query = "CREATE TABLE $hbn (\n";
while ( my @row = $sth2->fetchrow_array() ) {
foreach (@row) {
$_ = ' ' if !defined($_);
$query .= "$_ ";
}
$query .= ",\n";
}
$query .= " PRIMARY KEY ($PKeyn)\n";
$query .= ") ENGINE=InnoDB DEFAULT CHARSET=latin1\n\n";
print "$query\n";
$sth2->finish();
#execute creat table
$sth = $dbh->prepare($query);
$sth->execute();
# creat select random row fom oracle table
$SEL3 = qq[SELECT * FROM
( SELECT * FROM ].uc($hbn).qq[
ORDER BY dbms_random.value )
WHERE rownum < 11];
# excute SQL
$sth2 = $db1->prepare($SEL3);
$sth2->execute();
# assemble insert sql form results of Oracle query
while ( my @row = $sth2->fetchrow_array() ) {
$query = "INSERT INTO $hbn VALUES (";
my $j = 0;
foreach my $fl (@row) {
my $type2 = $sth2->{TYPE}[$j];
$j++;
switch ($type2) {
case 12 {$fl = '\''.$fl.'\'';}
case 93 {$fl = '\''.$fl.'\'';
$fl =~ s/JAN/01/;
$fl =~ s/FEB/02/;
$fl =~ s/MAR/03/;
$fl =~ s/APR/04/;
$fl =~ s/MAY/05/;
$fl =~ s/JUN/06/;
$fl =~ s/JUL/07/;
$fl =~ s/AUG/08/;
$fl =~ s/SEP/09/;
$fl =~ s/OCT/10/;
$fl =~ s/NOV/11/;
$fl =~ s/DEC/12/;
}
# else { print "previous case $type2 not true" }
}
$query .= "$fl";
$query .= "," if $j < $sth2->{NUM_OF_FIELDS};
}
$query .= ")";
print "$query\n";
# do the insert in mysql table
$sth = $dbh->prepare($query);
$sth->execute();
}
print "\n";
# an other way of getting the oracle table fields information
my $SEL1 = qq[select * from ].$hbn.qq[ where 0 = 1];
# excute SQL
$sth1 = $db1->prepare($SEL1);
$sth1->execute();
my $nf = $sth1->{NUM_OF_FIELDS};
for ( my $l = 0; $l < $nf; $l++ ) {
my $name1 = $sth1->{NAME}[$l];
my $type1 = $sth1->{TYPE}[$l];
my $prec1 = $sth1->{PRECISION}[$l];
my $scle1 = $sth1->{SCALE}[$l];
my $tn1=$db1->type_info($type1)->{TYPE_NAME};
$tn1 =~ s/ PRECISION//;
$tn1 =~ s/VARCHAR2/VARCHAR/;
$tn1 =~ s/DECIMAL/DOUBLE/;
print
"Field number $l: name $name1 of type $type1 $tn1 with precisi
+on $prec1,$scle1\n";
#print "$name1 $tn1($prec1";
#print ",$scle1" if $scle1 > 0;
#print ") ,\n";
}
tstampms();
# Close all connections
END {
$dbh->disconnect if defined($dbh);
$db1->disconnect if defined($db1);
}
| 