http://mysql.holywar.net/Downloads/Contrib/9168/177/oracledump.pl
#!/usr/bin/perl -w
use strict;
use vars qw ( @ARGV $dbh $db_database $db_host $db_port $db_user $db_password
$default_precision $default_scale $default_index_length
$with_table_comments $with_col_comments
$no_data $no_foreign_keys $extended_inserts $complete_inserts $add_drop_table
$add_locks $insert_delayed $lock_tables $table_type $net_buffer_length
$nls_date_format $nls_time_format $nls_timestamp_format $verbose $output_tail);
use DBI;
#############################################
#
# oracledump.pl - Dumps table(s) from an Oracle database to MySQL format
#
# Use ./oracledump.pl without args to see usage info.
# You may also change internal default settings below
#
#
# Written by Johan Andersson <johan@andersson.net>, May 2001
# Needs DBI and DBD::Oracle Perl modules to work.
#############################################
#
# Changes by Guilhem Bichot <guilhem.bichot@mines-paris.org>, April 2002 :
# - Fixed little bugs
# * a one-column primary key was never included in the CREATE TABLE statement
# * an Oracle RAW column was not given any MySQL type
# * statements starting by ';' were produced for empty tables with extended-inserts
# - Oracle RAW columns are converted into VARCHAR BINARY or BLOB, instead of
# VARCHAR or TEXT.
# - Added foreign keys information in final ALTER TABLE ADD FOREIGN KEY statements
# and ALTER TABLE ADD INDEX (commented) as InnoDB may need additional indexes
# for referencing and referenced columns
# - Added option --no-foreign-keys, as retrieving foreign keys information
# from the Oracle views takes a long time
# - Added option --table-type=... to specify the MySQL table type
# in the CREATE TABLE statement
# - Added option --net-buffer-length. If using extended inserts, switches to a new INSERT
# when the length of the INSERT statement reaches net-buffer-length.
# - Made the connection method a bit more versatile : if the user specifies the database name,
# it can either be a valid connection descriptor
# alias specified in the tnsnames.ora file, or (this is what I have added) be of the type
# SID@host or SID@host:port .
# - Suppressed the tnsnames.ora check. In certain situations you had to tell the script
# where the tnsnames.ora was, while the Oracle OCI connection was able to find it without help.
# Moreover, 'grep' does not exist on Windows. Finally, if you specify a wrong
# connection alias descriptor, the Oracle connection dies with "TNS : could not resolve
# service name" which is a sufficient error message. So we do not need to check in the script,
# the Oracle connection does it enough.
# - Lenghtened the help message a bit
### SETTINGS #################################
#
# All settings here are default settings that are used if they're
# not specified as argument.
#
# DATABASE SETUP ####
$db_database = $ENV{'ORACLE_SID'} if defined($ENV{'ORACLE_SID'}); # Default Oracle SID
$db_host = undef;
$db_user = getlogin(); # Default username
$db_password = ''; # Default password
# Oracle session parameters for date/time formats. Do not change if you not
# know exactly what you're doing!
$nls_date_format = 'RRRR-MM-DD';
$nls_time_format = 'HH24:MI:SSXFF';
$nls_timestamp_format = "$nls_date_format $nls_time_format";
# DATA TYPE SETTINGS ####
$default_precision = 18; # Default when precision is missing on NUMBER/FLOAT
$default_scale = 0; # - " " - scale - " " - - " " - - " " -
$default_index_length = 10; # This only affects indexes that contain BLOB fields.
# Sets how big in bytes the index should be for those columns.
# MISC. SETTINGS
$no_data = 0; # Sets whether to retrieve table data or just the table structure
$no_foreign_keys = 0; # Sets whether to retrieve foreign keys or not
$with_col_comments = 1; # Enable comments to be included for each column (if they exist in Oracle)
$with_table_comments = 1; # Same, but for comments for each table.
$extended_inserts = 0; # Use extended INSERT INTO syntax to insert
# multiple rows within one statement (MySQL)
$complete_inserts = 0; # Use of complete inserts adds list of table column
# names used, after the INTO word in INSERT INTO
# statements (MySQL)
# Eg.:
# complete: INSERT INTO foo (col1, col2) VALUES (1,2);
# compact (default): INSERT INTO foo VALUES (1,2);
$add_drop_table = 0; # Specifies if we are going to add DROP TABLE
# statements in the output
$add_locks = 0; # Specifies whether to use locks around insert
# statements or not (MySQL)
$insert_delayed = 0; # Specified wheter to use INSERT DELAYED or not (MySQL)
$lock_tables = 0; # If true, all tables will be locked for read before
# fetching anything from them (Oracle)
$net_buffer_length = 1_047_551 ;
$verbose = 0; # If enabled, program activities are printed out on STDERR
##############################################
# Trap some useful signals to avoid seg fault, DBI handles left open etc.
$SIG{'TERM'} = *interrupt;
$SIG{'QUIT'} = *interrupt;
$SIG{'INT'} = *interrupt;
# Put auto-flush on
$| = 1;
# int main( void )
# Main sub routine
sub main {
# Parse arguments and get tables..
my @arg_tables = parseArgs();
# Create a database handle
$dbh = db_connect($db_database, $db_host, $db_port, $db_user, $db_password);
my $sth;
# Use the ALTER SESSION command to change the date format used
$dbh->do("ALTER SESSION SET NLS_DATE_FORMAT = '$nls_date_format'");
$dbh->do("ALTER SESSION SET NLS_TIME_FORMAT = '$nls_time_format'");
$dbh->do("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = '$nls_timestamp_format'");
# Store tables in an array
my @tables;
# Check if any tables are specified as argument(s)
if($#arg_tables < 0) {
# Fetch all tables from USER_TABLES
$sth = $dbh->prepare("SELECT TABLE_NAME FROM USER_TABLES");
$sth->execute();
my @row;
while(@row = $sth->fetchrow_array()) {
push @tables, $row[0];
}
$sth->finish();
}
else {
while(<;@arg_tables>) {
# Check if table exists
$sth = $dbh->prepare("SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = ?");
$sth->execute($_);
if($sth->fetchrow_array()) {
push @tables, $_;
}
else {
warn "Table $_ does not existn";
}
$sth->finish();
}
}
# for each table
my $table;
foreach $table ( @tables ) {
print STDERR "* Checking table structure for table $tablen" if $verbose;
my @cols = describe_table($dbh, $table);
my %blobcols = (); # Keeps track on blob columns in indexes
my @colslist = ();
my $output = "";
my @quotecol = ();
my $lastcomment;
$output .= "nDROP TABLE IF EXISTS $table;" if $add_drop_table;
$output .= "nCREATE TABLE $table (";
for ( my $i = 0; $i <= $#cols; $i++ ) {
my(undef, undef, $datatype) = convert_dt($cols[$i]{'TYPE'}, $cols[$i]{'LENGTH'});
$output .= "nt".$cols[$i]{'NAME'}."t".$datatype;
$output .= " DEFAULT '".$cols[$i]{'DEFAULT'}."'" if defined($cols[$i]{'DEFAULT'});
$output .= " NOT NULL" if $cols[$i]{'NULL'} eq 'N';
$output .= "," if $i != $#cols;
$output .= "t# ".$cols[$i]{'COMMENT'} if defined($cols[$i]{'COMMENT'}) && $with_col_comments && $i != $#cols;
# We must save the last comment if such exists, otherwise we may get
# an error later since a separating comma may be printed on the wrong place
$lastcomment = $cols[$i]{'COMMENT'} if defined($cols[$i]{'COMMENT'}) && $with_col_comments && $i == $#cols;
# Save the insert values string
if($cols[$i]{'TYPE'} =~ /^(VAR)?CHAR2?|LONG( RAW)?|RAW|DATE$/) { #bug fix
$quotecol[$i] = 1;
}
else {
$quotecol[$i] = 0;
}
# Keep track on blob columns for indexes
if($datatype eq 'TEXT') {
$blobcols{$cols[$i]{'NAME'}} = 1;
}
# Keep a list of the columns to use when fetching data later
push @colslist, $cols[$i]{'NAME'};
}
# Get keys
print STDERR "* Fetching keys for table $tablen" if $verbose;
my %keys = get_keys($dbh, $table, $no_foreign_keys);
# Get primary key
my @pkcols = @{ $keys{'PRIMARY KEY'} };
my $pkstr = "";
if($#pkcols > -1) {
$output .= ",";
# print last column comment if such exist
if(defined($lastcomment)) {
$output .= "t# $lastcomment";
$lastcomment = undef;
}
$output .= "ntPRIMARY KEY (";
for(my $i=0; $i<=$#pkcols; $i++) {
# Keep track on columns in the PK in a string format (used later with indexes)
$pkstr .= $pkcols[$i]."-";
$output .= $pkcols[$i];
# Add index size if column is a blob column. Required by MySQL
$output .= "(".$default_index_length.")" if defined($blobcols{$pkcols[$i]});
$output .= "," unless $i == $#pkcols;
}
$output .= ")";
}
# Primary key done!
# Get foreign keys
my $type;
my @fkcols;
my $rtable;
my @rfkcols;
my $fkstr;
my $add_fk_statement="";
$type='FOREIGN KEY';
my %foreign_keys = %{ $keys{$type} };
my $number_of_foreign_keys = scalar keys(%foreign_keys);
if ($number_of_foreign_keys>=1) {
$add_fk_statement.= "nALTER TABLE $table" ;
}
my $j=0;
while( my($key) = each %foreign_keys ) {
@fkcols = @{ $foreign_keys{$key}{'COLUMNS'} };
$rtable = $foreign_keys{$key}{'RTABLE'} ;
@rfkcols = @{ $foreign_keys{$key}{'RCOLUMNS'} };
$fkstr = "ntADD ".$type." (";
$output_tail .= "n#ALTER TABLE $table ADD INDEX (";
for(my $i=0; $i<=$#fkcols; $i++) {
$fkstr .= $fkcols[$i];
$fkstr .= "," if $i != $#fkcols;
$output_tail .= $fkcols[$i];
$output_tail .= "," if $i != $#fkcols;
}
$fkstr .= ") REFERENCES ".$rtable." (";
$output_tail .= ");n#ALTER TABLE $rtable ADD INDEX (";
for(my $i=0; $i<=$#rfkcols; $i++) {
$fkstr .= $rfkcols[$i];
$fkstr .= "," if $i != $#rfkcols;
$output_tail .= $rfkcols[$i];
$output_tail .= "," if $i != $#rfkcols;
}
$fkstr .= ")";
$fkstr .= "," if $j != ($number_of_foreign_keys-1) ;
$output_tail .= ");";
$j++;
$add_fk_statement .= $fkstr;
}
if ($number_of_foreign_keys>=1) {
$output_tail .= "n#If the ALTER TABLE $table...ADD FOREIGN KEY... statement below fails with errno 150,n#uncomment some of the lines above and re-run the failed statementn";
$output_tail .= $add_fk_statement;
$output_tail .= ";n" ;
}
# Get indexes (non-unique and unique)
my @idxcols;
my $idxstr;
my $tmp_cols;
print STDERR "* Fetching indexes for table $tablen" if $verbose;
for $type ( 'UNIQUE', 'INDEX' ) {
my %indexes = %{ $keys{$type} };
while( my($key) = each %indexes ) {
@idxcols = @{ $indexes{$key} };
$idxstr = "nt".$type." ".$key." (";
$tmp_cols = "";
for(my $i=0; $i<=$#idxcols; $i++) {
# Keep track on index columns in a string format
$tmp_cols .= $idxcols[$i]."-";
$idxstr .= $idxcols[$i];
$idxstr .= "(".$default_index_length.")" if defined($blobcols{$idxcols[$i]});
$idxstr .= "," if $i != $#idxcols;
}
$idxstr .= ")";
# Check if this index is the PK index
# Since the PK already is specified above we should not
# include that index here.. But if the type not is UNIQUE
# this index is not the PK (just a non-unique duplicate of the
# PK index)
#
# You shouldn't be able to create such a index in Oracle
# (it would return an ORA-01408 error), but since MySQL may
# have duplicate indexes of different uniquenesses we do it
# the MySQL way to be sure..
if($tmp_cols ne $pkstr || $type eq 'INDEX') {
$output .= ",";
# Append last column comment if exists
if(defined($lastcomment)) {
$output .= "t# $lastcomment";
$lastcomment = undef;
}
$output .= $idxstr;
}
}
}
# Append last column comment if exists
if(defined($lastcomment)) {
$output .= "t# $lastcomment";
$lastcomment = undef;
}
$output .= "n)".( (defined($table_type)) ? " TYPE=$table_type" : "" ) .";n";
print STDERR "* Printing CREATE TABLE statement for table $tablen" if $verbose;
print $output;
if(!$no_data) {
# Fetch the data
print STDERR "* Obtaining locks on table $table on Oraclen" if $verbose && $lock_tables;
$dbh->do("LOCK TABLE $table IN SHARE MODE NOWAIT;") if $lock_tables;
print STDERR "* Preparing SELECT statement to fetch data from table $tablen" if $verbose;
$sth = $dbh->prepare("SELECT ".join(", ", @colslist)." FROM $table");
$sth->execute();
# Put together the insert statement. Take care of complete/"compact" options
my $insert_stmt = "INSERT ";
$insert_stmt .= "DELAYED " if $insert_delayed;
$insert_stmt .= "INTO $table ";
$insert_stmt .= "(".join(", ", @colslist).") " if $complete_inserts;
$insert_stmt .= "VALUES ";
my $istmt_nprinted = 1;
my $total_length;
my $tmpstr;
print STDERR "* Printing LOCK TABLES statement for table $tablen" if $verbose && $add_locks;
print "LOCK TABLES $table WRITE;n" if $add_locks;
print STDERR "* Starting to print INSERT INTO statement(s)n" if $verbose;
while(my @row = $sth->fetchrow_array()) {
$tmpstr = "(";
for(my $i=0; $i<=$#colslist; $i++) {
if(defined($row[$i]) && $quotecol[$i]) {
$tmpstr .= $dbh->quote(db_escape($row[$i]));
}
elsif(defined($row[$i]) && !$quotecol[$i]) {
$tmpstr .= $row[$i];
}
else {
$tmpstr .= "NULL";
}
$tmpstr .= "," if $i != $#colslist;
}
$tmpstr .= ")";
if(!$extended_inserts) {
print "$insert_stmt$tmpstr;n";
}
else {
if ($istmt_nprinted) {
print "$insert_stmt$tmpstr";$istmt_nprinted=0;
$total_length=length($insert_stmt)+length($tmpstr);
}
else {
if ( ($total_length+length($tmpstr))>($net_buffer_length-100) ) #100 if for security
#break the current extended INSERT statement and start a new one
{
print ";n$insert_stmt";
$total_length=length($insert_stmt)+length($tmpstr);
}
else {
print ",";$total_length+=length($tmpstr)+1;
}
print $tmpstr;
}
}
}
print ";n" if (($extended_inserts == 1) && ($istmt_nprinted == 0));
print "UNLOCK TABLES;n" if $add_locks;
$sth->finish();
print STDERR "* Done! Printed rows for table $tablen" if $verbose;
}
}
print STDERR "* Printing ALTER TABLE ADD FOREIGN KEY statements for all tablesn" if ($verbose && !$no_foreign_keys);
print $output_tail if !$no_foreign_keys;
$dbh->disconnect();
return 1;
}
# void interrupt ( void )
# This sub-routine is taking care of signals sent to the program
sub interrupt {
my ($sig) = @_;
print STDERR "Got $sig-signal; Issuing database disconnect and program exitn";
print STDERR "Warning: Active statements cannot be closed, exiting program now would cause a seg fault.nWarning: Quiting "safely" _without_ disconnecting from the database (to avoid seg. fault)n" if $dbh->{'ActiveKids'};
$dbh->disconnect() if ! $dbh->{'ActiveKids'};
exit(3);
}
# array describe_table( database_handle dbh, scalar table_name )
# Returns an array of hashes with info about the columns in the given table
sub describe_table {
my($dbh, $table_name) = @_;
# Get columns for the specified table
my $sth = $dbh->prepare("SELECT a.COLUMN_ID,
a.COLUMN_NAME,
a.DATA_TYPE,
a.DATA_LENGTH,
a.DATA_PRECISION,
a.DATA_SCALE,
a.NULLABLE,
a.DATA_DEFAULT,
b.COMMENTS
FROM USER_TAB_COLUMNS a, USER_COL_COMMENTS b
WHERE a.TABLE_NAME = ? AND b.TABLE_NAME = a.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME");
$sth->execute($table_name);
my @row;
my @columns;
my $length;
while(@row = $sth->fetchrow_array()) {
if($row[2] eq "NUMBER" || $row[2] eq "FLOAT") {
# Set length to default precision/scale values when information
# is missing in the data
$length = (defined($row[4])) ? $row[4] : $default_precision;
$length .= ",";
$length .= (defined($row[5])) ? $row[5] : $default_scale;
}
else {
$length = $row[3];
}
# Trim default data (and remove quotes that oracle stores from CREATE/ALTER command)
$row[7] =~ s/^'([^']+)'s*$/$1/ if defined($row[7]);
# Strip linebreaks in comments
$row[8] =~ s/n// if defined($row[8]);
# Build hash of info about this column
my %column = ( 'NAME' => $row[1],
'TYPE' => $row[2],
'LENGTH' => $length,
'NULL' => $row[6],
'DEFAULT' => $row[7],
'COMMENT' => $row[8] );
# Add the info about this column to the @columns array,
# set the index to the COLUMN_ID to get the "original" order
$columns[--$row[0]] = { %column };
}
$sth->finish();
# Return list of hashes
return @columns;
}
# hash get_keys( database_handle dbh, scalar table_name, scalar no_foreign_keys )
# returns hash of hashes with information of the constraints in the given table
#
# hash = {
# 'PRIMARY KEY' = [ COLNAME1, COLNAME2, ... ],
# 'FOREIGN KEY' = {
# KEYNAME => { 'COLUMNS' => [COLNAME1, COLNAME2, ... ],
# 'RTABLE' => RTABLENAME, "R" means referenced
# 'RCOLUMNS' => [ RCOLNAME1, RCOLNAME2, ... ]
# };
# },
# 'UNIQUE' = {
# KEYNAME => [ COLNAME1, COLNAME2, ... ]
# },
# 'INDEX' = {
# KEYNAME => [ COLNAME1, COLNAME2, ... ]
# }
# }
sub get_keys {
my($dbh, $table_name, $no_foreign_keys) = @_;
my %keys = (
'PRIMARY KEY' => [],
'FOREIGN KEY' => {},
'UNIQUE' => {},
'INDEX' => {}
);
my ( @row, $type, $sth );
# Fetch primary key
$type = 'PRIMARY KEY' ;
$sth = $dbh->prepare("SELECT a.constraint_name, a.column_name, a.position
FROM user_cons_columns a, user_constraints b
WHERE a.table_name=? AND a.constraint_name=b.constraint_name AND
b.constraint_type='P'
ORDER BY a.constraint_name, a.position");
$sth->execute($table_name);
while( @row = $sth->fetchrow_array()) {
$keys{$type}[--$row[2]] = $row[1];
}
$sth->finish();
# Primary key done!
# Retrieve foreign keys information
# Slow due to a 3-view join
if (!$no_foreign_keys)
{
$type = 'FOREIGN KEY' ;
$sth = $dbh->prepare("SELECT a.constraint_name, a.column_name, a.position,
c.table_name,c.column_name
FROM user_cons_columns a, user_constraints b,
user_cons_columns c
WHERE a.table_name=? AND a.constraint_name=b.constraint_name AND
b.constraint_type='R'
AND b.r_constraint_name=c.constraint_name
AND a.position=c.position
ORDER BY a.constraint_name");
$sth->execute($table_name);
while( @row = $sth->fetchrow_array()) {
$keys{$type}{$row[0]}{'COLUMNS'}[$row[2]-1] = $row[1];
$keys{$type}{$row[0]}{'RTABLE'} = $row[3];
$keys{$type}{$row[0]}{'RCOLUMNS'}[$row[2]-1] = $row[4];
}
$sth->finish();
# foreign keys done!
}
# Fetch indexes
$sth = $dbh->prepare("SELECT a.index_name, a.uniqueness, b.column_name, b.column_position
FROM user_indexes a, user_ind_columns b
WHERE a.table_name = ? AND b.table_name = a.table_name AND b.index_name = a.index_name
ORDER BY a.uniqueness, a.index_name");
$sth->execute($table_name);
while( @row = $sth->fetchrow_array() ) {
# Get index type; UNIQUE or INDEX (non-unique of course)
$type = "UNIQUE" if $row[1] eq 'UNIQUE';
$type = "INDEX" if $row[1] eq 'NONUNIQUE';
$keys{$type}{$row[0]}[--$row[3]] = $row[2];
}
$sth->finish();
return %keys;
}
# scalar db_escape ( scalar string )
# Escapes a string to be used within a SQL statement
sub db_escape {
my($str) = @_;
my $newstr = "";
for(my $i=0; $i<length($str); $i++) {
my $c = substr($str, $i, 1);
if(ord($c) == 10) {
$newstr .= chr(92).'n';
}
elsif(ord($c) == 13) {
$newstr .= chr(92).'r';
}
elsif(ord($c) == 9) {
$newstr .= chr(92).'t';
}
elsif(ord($c) == 34 || ord($c) == 92) {
$newstr .= chr(92).chr(ord($c));
}
else {
$newstr .= chr(ord($c));
}
}
return $newstr;
}
# list convert_dt( scalar datatype, scalar datalength )
# Converts datatype to mysql format and returns it
sub convert_dt {
my($datatype, $datalength) = @_;
############################################
# Rules are..:
# Datatype (ORA) Length Returns (MySQL)
# NUMBER Any NUMERIC
# DEC Any NUMERIC
# DECIMAL Any NUMERIC
# NUMERIC Any NUMERIC
# DOUBLE PRECISION Any NUMERIC
# FLOAT Any NUMERIC
# REAL Any NUMERIC
# SMALLINT Any SMALLINT
# VARCHAR <256 VARCHAR
# VARCHAR2 <256 VARCHAR
# CHAR <256 CHAR
# VARCHAR2 >255 TEXT
# VARCHAR >255 TEXT
# CHAR >255 TEXT
# LONG <256 VARCHAR
#LONGRAW does not exist, it is LONG RAW
# LONG RAW <256 VARCHAR BINARY (in Oracle RAW means binary data)
# RAW <256 VARCHAR BINARY
# LONG >255 TEXT
# LONG RAW >255 BLOB
# RAW >255 BLOB
# DATE - DATETIME (Since DATE in oracle can include time information!)
#
# List return consists of:
# 1. Datatype name
# 2. Datalength
# 3. Complete datatype spec. to be used within CREATE TABLE statement
#############################################
if($datalength eq '0') {
$datalength = 32767;
}
return ('NUMERIC', $datalength, 'NUMERIC('.$datalength.')') if $datatype =~ /^DEC(IMAL)?|NUMERIC|NUMBER|DOUBLE PRECISION|FLOAT|REAL$/;
return ('INTEGER', 38, 'INTEGER(38)') if $datatype =~ /^(SMALL)?INT(EGER)?$/;
return ('VARCHAR', $datalength, 'VARCHAR('.$datalength.')') if $datatype =~ /^N?VARCHAR2?|LONG$/ && $datalength <= 255;
return ('TEXT', '', 'TEXT') if $datatype =~ /^N?(VAR)?CHAR2?|LONG$/ && $datalength > 255;
return ('VARCHAR BINARY', $datalength, 'VARCHAR('.$datalength.') BINARY') if $datatype =~ /^LONG RAW|RAW$/ && $datalength <= 255;
return ('BLOB', '', 'BLOB') if $datatype =~ /^LONG RAW|RAW$/ && $datalength > 255;
return ('CHAR', $datalength, 'CHAR('.$datalength.')') if $datatype =~ /^N?CHAR$/ && $datalength <= 255;
return ('DATETIME', '', 'DATETIME') if $datatype eq 'DATE';
}
# db_connect( scalar database, scalar host, scalar port, scalar user, scalar password )
# database_handle connects to the database
sub db_connect {
my($database, $host, $port, $user, $password) = @_;
my $connect_string = defined($host) ? ("host=$host;sid=$database".(defined($port) ? ";port=$db_port" : "")) : $database ;
return DBI->connect( "dbi:Oracle:$connect_string", $user, $password,
{ AutoCommit => 0, RaiseError => 0, PrintError => 1, LongReadLen => 0, LongTruncOk => 1 } );
}
# array parseArgs( void )
# returns array of tables given as argument or invokes the printError() sub-routine
# if some error occurs, which exits the program
sub parseArgs {
if($#ARGV < 0) {
printUsage();
exit;
}
my $tmpcmd = undef;
my @arg_tables;
my $db_database_complete;
my $got_db = 0;
my $show_config_only = 0;
my $use_default_db = 0;
while(<;@ARGV>) {
my $arg = $_;
if(defined($tmpcmd)) {
SWITCH: for ($tmpcmd) {
/^user$/ && do {
$db_user = $arg;
$tmpcmd = undef;
last SWITCH;
};
/^password$/ && do {
$db_password = $arg;
$tmpcmd = undef;
last SWITCH;
};
}
}
else {
SWITCH: {
/^-h|--help$/ && do {
printUsage();
exit 0;
};
/^-u|--user=(.+)$/ && do {
if(defined($1)) {
$db_user = $1;
}
else {
$tmpcmd = 'user';
}
last SWITCH;
};
/^-p|--password=(.+)$/ && do {
if(defined($1)) {
$db_password = $1;
}
#!/usr/bin/perl -w
use strict;
use vars qw ( @ARGV $dbh $db_database $db_host $db_port $db_user $db_password
$default_precision $default_scale $default_index_length
$with_table_comments $with_col_comments
$no_data $no_foreign_keys $extended_inserts $complete_inserts $add_drop_table
$add_locks $insert_delayed $lock_tables $table_type $net_buffer_length
$nls_date_format $nls_time_format $nls_timestamp_format $verbose $output_tail);
use DBI;
#############################################
#
# oracledump.pl - Dumps table(s) from an Oracle database to MySQL format
#
# Use ./oracledump.pl without args to see usage info.
# You may also change internal default settings below
#
#
# Written by Johan Andersson <johan@andersson.net>, May 2001
# Needs DBI and DBD::Oracle Perl modules to work.
#############################################
#
# Changes by Guilhem Bichot <guilhem.bichot@mines-paris.org>, April 2002 :
# - Fixed little bugs
# * a one-column primary key was never included in the CREATE TABLE statement
# * an Oracle RAW column was not given any MySQL type
# * statements starting by ';' were produced for empty tables with extended-inserts
# - Oracle RAW columns are converted into VARCHAR BINARY or BLOB, instead of
# VARCHAR or TEXT.
# - Added foreign keys information in final ALTER TABLE ADD FOREIGN KEY statements
# and ALTER TABLE ADD INDEX (commented) as InnoDB may need additional indexes
# for referencing and referenced columns
# - Added option --no-foreign-keys, as retrieving foreign keys information
# from the Oracle views takes a long time
# - Added option --table-type=... to specify the MySQL table type
# in the CREATE TABLE statement
# - Added option --net-buffer-length. If using extended inserts, switches to a new INSERT
# when the length of the INSERT statement reaches net-buffer-length.
# - Made the connection method a bit more versatile : if the user specifies the database name,
# it can either be a valid connection descriptor
# alias specified in the tnsnames.ora file, or (this is what I have added) be of the type
# SID@host or SID@host:port .
# - Suppressed the tnsnames.ora check. In certain situations you had to tell the script
# where the tnsnames.ora was, while the Oracle OCI connection was able to find it without help.
# Moreover, 'grep' does not exist on Windows. Finally, if you specify a wrong
# connection alias descriptor, the Oracle connection dies with "TNS : could not resolve
# service name" which is a sufficient error message. So we do not need to check in the script,
# the Oracle connection does it enough.
# - Lenghtened the help message a bit
### SETTINGS #################################
#
# All settings here are default settings that are used if they're
# not specified as argument.
#
# DATABASE SETUP ####
$db_database = $ENV{'ORACLE_SID'} if defined($ENV{'ORACLE_SID'}); # Default Oracle SID
$db_host = undef;
$db_user = getlogin(); # Default username
$db_password = ''; # Default password
# Oracle session parameters for date/time formats. Do not change if you not
# know exactly what you're doing!
$nls_date_format = 'RRRR-MM-DD';
$nls_time_format = 'HH24:MI:SSXFF';
$nls_timestamp_format = "$nls_date_format $nls_time_format";
# DATA TYPE SETTINGS ####
$default_precision = 18; # Default when precision is missing on NUMBER/FLOAT
$default_scale = 0; # - " " - scale - " " - - " " - - " " -
$default_index_length = 10; # This only affects indexes that contain BLOB fields.
# Sets how big in bytes the index should be for those columns.
# MISC. SETTINGS
$no_data = 0; # Sets whether to retrieve table data or just the table structure
$no_foreign_keys = 0; # Sets whether to retrieve foreign keys or not
$with_col_comments = 1; # Enable comments to be included for each column (if they exist in Oracle)
$with_table_comments = 1; # Same, but for comments for each table.
$extended_inserts = 0; # Use extended INSERT INTO syntax to insert
# multiple rows within one statement (MySQL)
$complete_inserts = 0; # Use of complete inserts adds list of table column
# names used, after the INTO word in INSERT INTO
# statements (MySQL)
# Eg.:
# complete: INSERT INTO foo (col1, col2) VALUES (1,2);
# compact (default): INSERT INTO foo VALUES (1,2);
$add_drop_table = 0; # Specifies if we are going to add DROP TABLE
# statements in the output
$add_locks = 0; # Specifies whether to use locks around insert
# statements or not (MySQL)
$insert_delayed = 0; # Specified wheter to use INSERT DELAYED or not (MySQL)
$lock_tables = 0; # If true, all tables will be locked for read before
# fetching anything from them (Oracle)
$net_buffer_length = 1_047_551 ;
$verbose = 0; # If enabled, program activities are printed out on STDERR
##############################################
# Trap some useful signals to avoid seg fault, DBI handles left open etc.
$SIG{'TERM'} = *interrupt;
$SIG{'QUIT'} = *interrupt;
$SIG{'INT'} = *interrupt;
# Put auto-flush on
$| = 1;
# int main( void )
# Main sub routine
sub main {
# Parse arguments and get tables..
my @arg_tables = parseArgs();
# Create a database handle
$dbh = db_connect($db_database, $db_host, $db_port, $db_user, $db_password);
my $sth;
# Use the ALTER SESSION command to change the date format used
$dbh->do("ALTER SESSION SET NLS_DATE_FORMAT = '$nls_date_format'");
$dbh->do("ALTER SESSION SET NLS_TIME_FORMAT = '$nls_time_format'");
$dbh->do("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = '$nls_timestamp_format'");
# Store tables in an array
my @tables;
# Check if any tables are specified as argument(s)
if($#arg_tables < 0) {
# Fetch all tables from USER_TABLES
$sth = $dbh->prepare("SELECT TABLE_NAME FROM USER_TABLES");
$sth->execute();
my @row;
while(@row = $sth->fetchrow_array()) {
push @tables, $row[0];
}
$sth->finish();
}
else {
while(<;@arg_tables>) {
# Check if table exists
$sth = $dbh->prepare("SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = ?");
$sth->execute($_);
if($sth->fetchrow_array()) {
push @tables, $_;
}
else {
warn "Table $_ does not existn";
}
$sth->finish();
}
}
# for each table
my $table;
foreach $table ( @tables ) {
print STDERR "* Checking table structure for table $tablen" if $verbose;
my @cols = describe_table($dbh, $table);
my %blobcols = (); # Keeps track on blob columns in indexes
my @colslist = ();
my $output = "";
my @quotecol = ();
my $lastcomment;
$output .= "nDROP TABLE IF EXISTS $table;" if $add_drop_table;
$output .= "nCREATE TABLE $table (";
for ( my $i = 0; $i <= $#cols; $i++ ) {
my(undef, undef, $datatype) = convert_dt($cols[$i]{'TYPE'}, $cols[$i]{'LENGTH'});
$output .= "nt".$cols[$i]{'NAME'}."t".$datatype;
$output .= " DEFAULT '".$cols[$i]{'DEFAULT'}."'" if defined($cols[$i]{'DEFAULT'});
$output .= " NOT NULL" if $cols[$i]{'NULL'} eq 'N';
$output .= "," if $i != $#cols;
$output .= "t# ".$cols[$i]{'COMMENT'} if defined($cols[$i]{'COMMENT'}) && $with_col_comments && $i != $#cols;
# We must save the last comment if such exists, otherwise we may get
# an error later since a separating comma may be printed on the wrong place
$lastcomment = $cols[$i]{'COMMENT'} if defined($cols[$i]{'COMMENT'}) && $with_col_comments && $i == $#cols;
# Save the insert values string
if($cols[$i]{'TYPE'} =~ /^(VAR)?CHAR2?|LONG( RAW)?|RAW|DATE$/) { #bug fix
$quotecol[$i] = 1;
}
else {
$quotecol[$i] = 0;
}
# Keep track on blob columns for indexes
if($datatype eq 'TEXT') {
$blobcols{$cols[$i]{'NAME'}} = 1;
}
# Keep a list of the columns to use when fetching data later
push @colslist, $cols[$i]{'NAME'};
}
# Get keys
print STDERR "* Fetching keys for table $tablen" if $verbose;
my %keys = get_keys($dbh, $table, $no_foreign_keys);
# Get primary key
my @pkcols = @{ $keys{'PRIMARY KEY'} };
my $pkstr = "";
if($#pkcols > -1) {
$output .= ",";
# print last column comment if such exist
if(defined($lastcomment)) {
$output .= "t# $lastcomment";
$lastcomment = undef;
}
$output .= "ntPRIMARY KEY (";
for(my $i=0; $i<=$#pkcols; $i++) {
# Keep track on columns in the PK in a string format (used later with indexes)
$pkstr .= $pkcols[$i]."-";
$output .= $pkcols[$i];
# Add index size if column is a blob column. Required by MySQL
$output .= "(".$default_index_length.")" if defined($blobcols{$pkcols[$i]});
$output .= "," unless $i == $#pkcols;
}
$output .= ")";
}
# Primary key done!
# Get foreign keys
my $type;
my @fkcols;
my $rtable;
my @rfkcols;
my $fkstr;
my $add_fk_statement="";
$type='FOREIGN KEY';
my %foreign_keys = %{ $keys{$type} };
my $number_of_foreign_keys = scalar keys(%foreign_keys);
if ($number_of_foreign_keys>=1) {
$add_fk_statement.= "nALTER TABLE $table" ;
}
my $j=0;
while( my($key) = each %foreign_keys ) {
@fkcols = @{ $foreign_keys{$key}{'COLUMNS'} };
$rtable = $foreign_keys{$key}{'RTABLE'} ;
@rfkcols = @{ $foreign_keys{$key}{'RCOLUMNS'} };
$fkstr = "ntADD ".$type." (";
$output_tail .= "n#ALTER TABLE $table ADD INDEX (";
for(my $i=0; $i<=$#fkcols; $i++) {
$fkstr .= $fkcols[$i];
$fkstr .= "," if $i != $#fkcols;
$output_tail .= $fkcols[$i];
$output_tail .= "," if $i != $#fkcols;
}
$fkstr .= ") REFERENCES ".$rtable." (";
$output_tail .= ");n#ALTER TABLE $rtable ADD INDEX (";
for(my $i=0; $i<=$#rfkcols; $i++) {
$fkstr .= $rfkcols[$i];
$fkstr .= "," if $i != $#rfkcols;
$output_tail .= $rfkcols[$i];
$output_tail .= "," if $i != $#rfkcols;
}
$fkstr .= ")";
$fkstr .= "," if $j != ($number_of_foreign_keys-1) ;
$output_tail .= ");";
$j++;
$add_fk_statement .= $fkstr;
}
if ($number_of_foreign_keys>=1) {
$output_tail .= "n#If the ALTER TABLE $table...ADD FOREIGN KEY... statement below fails with errno 150,n#uncomment some of the lines above and re-run the failed statementn";
$output_tail .= $add_fk_statement;
$output_tail .= ";n" ;
}
# Get indexes (non-unique and unique)
my @idxcols;
my $idxstr;
my $tmp_cols;
print STDERR "* Fetching indexes for table $tablen" if $verbose;
for $type ( 'UNIQUE', 'INDEX' ) {
my %indexes = %{ $keys{$type} };
while( my($key) = each %indexes ) {
@idxcols = @{ $indexes{$key} };
$idxstr = "nt".$type." ".$key." (";
$tmp_cols = "";
for(my $i=0; $i<=$#idxcols; $i++) {
# Keep track on index columns in a string format
$tmp_cols .= $idxcols[$i]."-";
$idxstr .= $idxcols[$i];
$idxstr .= "(".$default_index_length.")" if defined($blobcols{$idxcols[$i]});
$idxstr .= "," if $i != $#idxcols;
}
$idxstr .= ")";
# Check if this index is the PK index
# Since the PK already is specified above we should not
# include that index here.. But if the type not is UNIQUE
# this index is not the PK (just a non-unique duplicate of the
# PK index)
#
# You shouldn't be able to create such a index in Oracle
# (it would return an ORA-01408 error), but since MySQL may
# have duplicate indexes of different uniquenesses we do it
# the MySQL way to be sure..
if($tmp_cols ne $pkstr || $type eq 'INDEX') {
$output .= ",";
# Append last column comment if exists
if(defined($lastcomment)) {
$output .= "t# $lastcomment";
$lastcomment = undef;
}
$output .= $idxstr;
}
}
}
# Append last column comment if exists
if(defined($lastcomment)) {
$output .= "t# $lastcomment";
$lastcomment = undef;
}
$output .= "n)".( (defined($table_type)) ? " TYPE=$table_type" : "" ) .";n";
print STDERR "* Printing CREATE TABLE statement for table $tablen" if $verbose;
print $output;
if(!$no_data) {
# Fetch the data
print STDERR "* Obtaining locks on table $table on Oraclen" if $verbose && $lock_tables;
$dbh->do("LOCK TABLE $table IN SHARE MODE NOWAIT;") if $lock_tables;
print STDERR "* Preparing SELECT statement to fetch data from table $tablen" if $verbose;
$sth = $dbh->prepare("SELECT ".join(", ", @colslist)." FROM $table");
$sth->execute();
# Put together the insert statement. Take care of complete/"compact" options
my $insert_stmt = "INSERT ";
$insert_stmt .= "DELAYED " if $insert_delayed;
$insert_stmt .= "INTO $table ";
$insert_stmt .= "(".join(", ", @colslist).") " if $complete_inserts;
$insert_stmt .= "VALUES ";
my $istmt_nprinted = 1;
my $total_length;
my $tmpstr;
print STDERR "* Printing LOCK TABLES statement for table $tablen" if $verbose && $add_locks;
print "LOCK TABLES $table WRITE;n" if $add_locks;
print STDERR "* Starting to print INSERT INTO statement(s)n" if $verbose;
while(my @row = $sth->fetchrow_array()) {
$tmpstr = "(";
for(my $i=0; $i<=$#colslist; $i++) {
if(defined($row[$i]) && $quotecol[$i]) {
$tmpstr .= $dbh->quote(db_escape($row[$i]));
}
elsif(defined($row[$i]) && !$quotecol[$i]) {
$tmpstr .= $row[$i];
}
else {
$tmpstr .= "NULL";
}
$tmpstr .= "," if $i != $#colslist;
}
$tmpstr .= ")";
if(!$extended_inserts) {
print "$insert_stmt$tmpstr;n";
}
else {
if ($istmt_nprinted) {
print "$insert_stmt$tmpstr";$istmt_nprinted=0;
$total_length=length($insert_stmt)+length($tmpstr);
}
else {
if ( ($total_length+length($tmpstr))>($net_buffer_length-100) ) #100 if for security
#break the current extended INSERT statement and start a new one
{
print ";n$insert_stmt";
$total_length=length($insert_stmt)+length($tmpstr);
}
else {
print ",";$total_length+=length($tmpstr)+1;
}
print $tmpstr;
}
}
}
print ";n" if (($extended_inserts == 1) && ($istmt_nprinted == 0));
print "UNLOCK TABLES;n" if $add_locks;
$sth->finish();
print STDERR "* Done! Printed rows for table $tablen" if $verbose;
}
}
print STDERR "* Printing ALTER TABLE ADD FOREIGN KEY statements for all tablesn" if ($verbose && !$no_foreign_keys);
print $output_tail if !$no_foreign_keys;
$dbh->disconnect();
return 1;
}
# void interrupt ( void )
# This sub-routine is taking care of signals sent to the program
sub interrupt {
my ($sig) = @_;
print STDERR "Got $sig-signal; Issuing database disconnect and program exitn";
print STDERR "Warning: Active statements cannot be closed, exiting program now would cause a seg fault.nWarning: Quiting "safely" _without_ disconnecting from the database (to avoid seg. fault)n" if $dbh->{'ActiveKids'};
$dbh->disconnect() if ! $dbh->{'ActiveKids'};
exit(3);
}
# array describe_table( database_handle dbh, scalar table_name )
# Returns an array of hashes with info about the columns in the given table
sub describe_table {
my($dbh, $table_name) = @_;
# Get columns for the specified table
my $sth = $dbh->prepare("SELECT a.COLUMN_ID,
a.COLUMN_NAME,
a.DATA_TYPE,
a.DATA_LENGTH,
a.DATA_PRECISION,
a.DATA_SCALE,
a.NULLABLE,
a.DATA_DEFAULT,
b.COMMENTS
FROM USER_TAB_COLUMNS a, USER_COL_COMMENTS b
WHERE a.TABLE_NAME = ? AND b.TABLE_NAME = a.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME");
$sth->execute($table_name);
my @row;
my @columns;
my $length;
while(@row = $sth->fetchrow_array()) {
if($row[2] eq "NUMBER" || $row[2] eq "FLOAT") {
# Set length to default precision/scale values when information
# is missing in the data
$length = (defined($row[4])) ? $row[4] : $default_precision;
$length .= ",";
$length .= (defined($row[5])) ? $row[5] : $default_scale;
}
else {
$length = $row[3];
}
# Trim default data (and remove quotes that oracle stores from CREATE/ALTER command)
$row[7] =~ s/^'([^']+)'s*$/$1/ if defined($row[7]);
# Strip linebreaks in comments
$row[8] =~ s/n// if defined($row[8]);
# Build hash of info about this column
my %column = ( 'NAME' => $row[1],
'TYPE' => $row[2],
'LENGTH' => $length,
'NULL' => $row[6],
'DEFAULT' => $row[7],
'COMMENT' => $row[8] );
# Add the info about this column to the @columns array,
# set the index to the COLUMN_ID to get the "original" order
$columns[--$row[0]] = { %column };
}
$sth->finish();
# Return list of hashes
return @columns;
}
# hash get_keys( database_handle dbh, scalar table_name, scalar no_foreign_keys )
# returns hash of hashes with information of the constraints in the given table
#
# hash = {
# 'PRIMARY KEY' = [ COLNAME1, COLNAME2, ... ],
# 'FOREIGN KEY' = {
# KEYNAME => { 'COLUMNS' => [COLNAME1, COLNAME2, ... ],
# 'RTABLE' => RTABLENAME, "R" means referenced
# 'RCOLUMNS' => [ RCOLNAME1, RCOLNAME2, ... ]
# };
# },
# 'UNIQUE' = {
# KEYNAME => [ COLNAME1, COLNAME2, ... ]
# },
# 'INDEX' = {
# KEYNAME => [ COLNAME1, COLNAME2, ... ]
# }
# }
sub get_keys {
my($dbh, $table_name, $no_foreign_keys) = @_;
my %keys = (
'PRIMARY KEY' => [],
'FOREIGN KEY' => {},
'UNIQUE' => {},
'INDEX' => {}
);
my ( @row, $type, $sth );
# Fetch primary key
$type = 'PRIMARY KEY' ;
$sth = $dbh->prepare("SELECT a.constraint_name, a.column_name, a.position
FROM user_cons_columns a, user_constraints b
WHERE a.table_name=? AND a.constraint_name=b.constraint_name AND
b.constraint_type='P'
ORDER BY a.constraint_name, a.position");
$sth->execute($table_name);
while( @row = $sth->fetchrow_array()) {
$keys{$type}[--$row[2]] = $row[1];
}
$sth->finish();
# Primary key done!
# Retrieve foreign keys information
# Slow due to a 3-view join
if (!$no_foreign_keys)
{
$type = 'FOREIGN KEY' ;
$sth = $dbh->prepare("SELECT a.constraint_name, a.column_name, a.position,
c.table_name,c.column_name
FROM user_cons_columns a, user_constraints b,
user_cons_columns c
WHERE a.table_name=? AND a.constraint_name=b.constraint_name AND
b.constraint_type='R'
AND b.r_constraint_name=c.constraint_name
AND a.position=c.position
ORDER BY a.constraint_name");
$sth->execute($table_name);
while( @row = $sth->fetchrow_array()) {
$keys{$type}{$row[0]}{'COLUMNS'}[$row[2]-1] = $row[1];
$keys{$type}{$row[0]}{'RTABLE'} = $row[3];
$keys{$type}{$row[0]}{'RCOLUMNS'}[$row[2]-1] = $row[4];
}
$sth->finish();
# foreign keys done!
}
# Fetch indexes
$sth = $dbh->prepare("SELECT a.index_name, a.uniqueness, b.column_name, b.column_position
FROM user_indexes a, user_ind_columns b
WHERE a.table_name = ? AND b.table_name = a.table_name AND b.index_name = a.index_name
ORDER BY a.uniqueness, a.index_name");
$sth->execute($table_name);
while( @row = $sth->fetchrow_array() ) {
# Get index type; UNIQUE or INDEX (non-unique of course)
$type = "UNIQUE" if $row[1] eq 'UNIQUE';
$type = "INDEX" if $row[1] eq 'NONUNIQUE';
$keys{$type}{$row[0]}[--$row[3]] = $row[2];
}
$sth->finish();
return %keys;
}
# scalar db_escape ( scalar string )
# Escapes a string to be used within a SQL statement
sub db_escape {
my($str) = @_;
my $newstr = "";
for(my $i=0; $i<length($str); $i++) {
my $c = substr($str, $i, 1);
if(ord($c) == 10) {
$newstr .= chr(92).'n';
}
elsif(ord($c) == 13) {
$newstr .= chr(92).'r';
}
elsif(ord($c) == 9) {
$newstr .= chr(92).'t';
}
elsif(ord($c) == 34 || ord($c) == 92) {
$newstr .= chr(92).chr(ord($c));
}
else {
$newstr .= chr(ord($c));
}
}
return $newstr;
}
# list convert_dt( scalar datatype, scalar datalength )
# Converts datatype to mysql format and returns it
sub convert_dt {
my($datatype, $datalength) = @_;
############################################
# Rules are..:
# Datatype (ORA) Length Returns (MySQL)
# NUMBER Any NUMERIC
# DEC Any NUMERIC
# DECIMAL Any NUMERIC
# NUMERIC Any NUMERIC
# DOUBLE PRECISION Any NUMERIC
# FLOAT Any NUMERIC
# REAL Any NUMERIC
# SMALLINT Any SMALLINT
# VARCHAR <256 VARCHAR
# VARCHAR2 <256 VARCHAR
# CHAR <256 CHAR
# VARCHAR2 >255 TEXT
# VARCHAR >255 TEXT
# CHAR >255 TEXT
# LONG <256 VARCHAR
#LONGRAW does not exist, it is LONG RAW
# LONG RAW <256 VARCHAR BINARY (in Oracle RAW means binary data)
# RAW <256 VARCHAR BINARY
# LONG >255 TEXT
# LONG RAW >255 BLOB
# RAW >255 BLOB
# DATE - DATETIME (Since DATE in oracle can include time information!)
#
# List return consists of:
# 1. Datatype name
# 2. Datalength
# 3. Complete datatype spec. to be used within CREATE TABLE statement
#############################################
if($datalength eq '0') {
$datalength = 32767;
}
return ('NUMERIC', $datalength, 'NUMERIC('.$datalength.')') if $datatype =~ /^DEC(IMAL)?|NUMERIC|NUMBER|DOUBLE PRECISION|FLOAT|REAL$/;
return ('INTEGER', 38, 'INTEGER(38)') if $datatype =~ /^(SMALL)?INT(EGER)?$/;
return ('VARCHAR', $datalength, 'VARCHAR('.$datalength.')') if $datatype =~ /^N?VARCHAR2?|LONG$/ && $datalength <= 255;
return ('TEXT', '', 'TEXT') if $datatype =~ /^N?(VAR)?CHAR2?|LONG$/ && $datalength > 255;
return ('VARCHAR BINARY', $datalength, 'VARCHAR('.$datalength.') BINARY') if $datatype =~ /^LONG RAW|RAW$/ && $datalength <= 255;
return ('BLOB', '', 'BLOB') if $datatype =~ /^LONG RAW|RAW$/ && $datalength > 255;
return ('CHAR', $datalength, 'CHAR('.$datalength.')') if $datatype =~ /^N?CHAR$/ && $datalength <= 255;
return ('DATETIME', '', 'DATETIME') if $datatype eq 'DATE';
}
# db_connect( scalar database, scalar host, scalar port, scalar user, scalar password )
# database_handle connects to the database
sub db_connect {
my($database, $host, $port, $user, $password) = @_;
my $connect_string = defined($host) ? ("host=$host;sid=$database".(defined($port) ? ";port=$db_port" : "")) : $database ;
return DBI->connect( "dbi:Oracle:$connect_string", $user, $password,
{ AutoCommit => 0, RaiseError => 0, PrintError => 1, LongReadLen => 0, LongTruncOk => 1 } );
}
# array parseArgs( void )
# returns array of tables given as argument or invokes the printError() sub-routine
# if some error occurs, which exits the program
sub parseArgs {
if($#ARGV < 0) {
printUsage();
exit;
}
my $tmpcmd = undef;
my @arg_tables;
my $db_database_complete;
my $got_db = 0;
my $show_config_only = 0;
my $use_default_db = 0;
while(<;@ARGV>) {
my $arg = $_;
if(defined($tmpcmd)) {
SWITCH: for ($tmpcmd) {
/^user$/ && do {
$db_user = $arg;
$tmpcmd = undef;
last SWITCH;
};
/^password$/ && do {
$db_password = $arg;
$tmpcmd = undef;
last SWITCH;
};
}
}
else {
SWITCH: {
/^-h|--help$/ && do {
printUsage();
exit 0;
};
/^-u|--user=(.+)$/ && do {
if(defined($1)) {
$db_user = $1;
}
else {
$tmpcmd = 'user';
}
last SWITCH;
};
/^-p|--password=(.+)$/ && do {
if(defined($1)) {
$db_password = $1;
}
Rss Feed