3 # Copyright 2013, Equinox Software, Inc.
5 # Author: Galen Charlton <gmc@esilibrary.com>
7 # This program is free software; you can redistribute it and/or
8 # modify it under the terms of the GNU General Public License
9 # as published by the Free Software Foundation; either version 2
10 # of the License, or (at your option) any later version.
12 # This program is distributed in the hope that it will be useful,
13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with this program; if not, write to the Free Software
19 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
29 my $host = 'localhost';
30 my $sid = $ENV{ORACLE_SID};
38 my $result = GetOptions(
45 'source-charset=s' => \$src_charset,
46 'help' => \$show_help,
49 if ($show_help || !$result || !$out || !$query || !$user || !$pw) {
51 $0: dump results of query on Oracle database to file for loading into PostgreSQL
54 [--sid oracle_sid] [--host oracle_host] --user oracle_user --pw oracle_password \\
56 --out output_tsv_file [--help]
62 my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user, $pw) or croak "Cannot connect to the database";
63 $dbh->do("ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'");
65 open my $outfh, '>', $out or croak "Cannot open output file $out: $!\n";
66 binmode $outfh, ':raw';
68 dump_query_output($query, $outfh);
74 sub dump_query_output {
77 my $sth = $dbh->prepare($query);
79 while (my $row = $sth->fetchrow_arrayref()) {
80 my @data = map { normalize_value_for_tsv($_) } @$row;
81 my $str = join("\t", @data);
83 print $fh encode('utf8', "$str\n");
88 sub normalize_value_for_tsv {
92 $val =~ s/\0//g; # FIXME: not dealing with BLOBs for now
100 return decode($src_charset, $val);