: # use perl:
eval 'exec perl -w $0 $*'
    if 0;

# ora_export.pl: Export and import DSV files from and into Oracle
# usage: ora_export.pl [-i] tablename
# -i imports data from stdin, otherwise exports data to stdout.
# Tablename must be given. Prints some feedback on stderr.
# ORACLE_HOME and ORACLE_SID must be set in the environment.

# set Oracle username/password here:
$dbuser = "hans";
$dbpasswd = "wurst";

# connect to the db:
if (!defined($ENV{"ORACLE_HOME"}) || !defined($ENV{"ORACLE_SID"})) {
    die "ORACLE_HOME and/or ORACLE_SID not set!\n";
}
use DBI;
$dbh = DBI->connect("DBI:Oracle:", $dbuser, $dbpasswd);
if (!defined($dbh)) {
    die "Can't connect to DB!\n";
}

# what to do?
$todo = "export";
$table = "";
while ($arg = shift(@ARGV)) {
    if ($arg eq "-i") {
	$todo = "import";
    } else {
	$table = $arg;
    }
}
if ($table eq "") {
    die "Usage: $0 [-i] tablename\n";
}
print STDERR "${todo}ing table $table\n";

if ($todo eq "export") {
    $sth = $dbh->prepare("select * from $table");
    $sth->execute;
    $r = 0;
    while (@row = $sth->fetchrow_array) {
	$numcols = @row;
	for ($i = 0; $i < $numcols; $i++) {
	    if (defined($row[$i])) {
		# escape backslashes, commas and newlines with a backslash
		$row[$i] =~ s/([\\,\n])/\\$1/g;
	    } else {
		$row[$i] = "";
	    }
	    # print columns separated by a comma
	    if ($i > 0) {
		print ",";
	    }
	    print $row[$i];
	}
	# separate rows by a newline
	print "\n";
	# Give feedback every 100 and 1000 rows
	$r++;
	if ($r % 100 == 0) {
	    print STDERR ".";
	}
	if ($r % 1000 == 0) {
	    print STDERR $r;
	}
    }
    $sth->finish;
    print STDERR "\nexported $r rows\n";
} elsif ($todo eq "import") {
    # get the data type of each column, remember if values must be quoted
    # in "insert into" statements
    $sth = $dbh->prepare("select column_name,data_type from user_tab_columns "
	. "where table_name='" . uc($table) . "' order by column_id");
    $sth->execute;
    $i = 0;
    while (@row = $sth->fetchrow_array) {
	# print STDERR "type of column $row[0] is $row[1]\n";
	$quote[$i] = ($row[1] =~ /char|date|lob/i);
	$i++;
    }
    $sth->finish;
    # remember number of cols
    $numcols = $i;
    # read the input file
    $r = 0;
    while ($line = <>) {
      redo_row:
	chomp($line);
	# get the length and number of backslashes at the end of the line.
	($len, $bsc) = count_bs($line);
	if ($bsc % 2) {
	    # if odd, the last backslash was inserted at export to escape
	    # a newline within the data. Replace it with a newline and
	    # append the next line.
	    substr($line, $len, 1) = "\n";
	    $line .= <>;
	    goto redo_row;
	}
	# now we have one row. separate it into columns.
	@row = split(/,/, $line);
	# build the insert statement
	$query = "insert into $table values (";
	$sep = "";
	# pointer into @row
	$c = 0;
	for ($i = 0; $i < $numcols; $i++) {
	    # if the table has more columns than the data file, append empty
	    # columns
	    if (!defined($row[$c])) {
		$row[$c] = $quote[$i] ? "" : "0";
	    }
	  redo_col:
	    # get the length of the column and the number of backslashes at its
	    # end
	    ($len, $bsc) = count_bs($row[$c]);
	    if ($bsc % 2) {
		# if odd, the last backslash was inserted at export to escape
		# a comma within the data. Replace it with a comma and
		# concat this one and the next column.
		substr($row[$c], $len, 1) = ",";
		$c++;
		# if the table has more columns than the data file, append
		# empty columns
		if (!defined($row[$c])) {
		    $row[$c] = $quote[$i] ? "" : "0";
		}
		# concat thw two columns.
		$row[$c] = $row[$c - 1] . $row[$c];
		goto redo_col;
	    }
	    # now we have on complete column. unescape backslahes.
	    $row[$c] =~ s/\\\\/\\/g;
	    if ($quote[$i]) {
		# if this column must be quoted, duplicate all single quotes
		# within it and enclose the whole column in single quotes
		$row[$c] =~ s/'/''/g;
		$query .= "$sep'$row[$c]'";
	    } else {
		$query .= "$sep$row[$c]";
	    }
	    $sep = ",";
	    $c++;
	}
	$query .= ")";
	# print STDERR $query, "\n";
	$dbh->do($query);
	# Give feedback every 100 and 1000 rows
	$r++;
	if ($r % 100 == 0) {
	    print STDERR ".";
	}
	if ($r % 1000 == 0) {
	    print STDERR $r;
	}
    }
    print STDERR "\nimported $r rows\n";
}

# done
$dbh->disconnect;
exit(0);

# return the length of a string and the number of backslashes at its end
sub count_bs
{
    my $len = length($_[0]) - 1;
    my $l = $len;
    my $cnt = 0;
    while (($l >= 0) && (substr($_[0], $l, 1) eq "\\")) {
	$cnt++;
	$l--;
    }
    return ($len, $cnt);
}