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 $column_prefix = '';
42 my $result = GetOptions(
50 'pg-table=s' => \$pg_table,
51 'column-prefix=s' => \$column_prefix,
52 'inherits-from=s' => \$base_table,
53 'source-charset=s' => \$src_charset,
54 'help' => \$show_help,
57 if ($show_help || !$result || !$out || !$sql || !$user || !$pw || !$table || !$pg_table) {
59 $0: dump contents of Oracle table to file for loading into PostgreSQL
62 [--sid oracle_sid] [--host oracle_host] --user oracle_user --pw oracle_password \\
63 --table oracle_table_name \\
64 --pg-table destination_pg_table_name \\
65 --out output_tsv_file --sql output_table_create_sql_file \\
66 [--column-prefix column_prefix] [--inherits-from base_pg_table] [--help]
72 my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user, $pw) or croak "Cannot connect to the database";
73 $dbh->do("ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'");
75 open my $outfh, '>', $out or croak "Cannot open output file $out: $!\n";
76 binmode $outfh, ':raw';
77 open my $sqlfh, '>', $sql or croak "Cannot open output file $sql: $!\n";
78 binmode $sqlfh, ':raw';
80 export_table(uc $table, $outfh, $sqlfh, $out);
92 my $cols = get_columns($table);
93 my $query = 'SELECT ' . join(', ', map { $_->{name} } @$cols) . " FROM $table";
94 my $sth = $dbh->prepare($query);
96 while (my $row = $sth->fetchrow_arrayref()) {
97 my @data = map { normalize_value_for_tsv($_) } @$row;
98 my $str = join("\t", @data);
100 print $fh encode('utf8', "$str\n");
104 print $sqlfh "CREATE TABLE $pg_table (\n";
105 print $sqlfh join(",\n", map { $column_prefix . lc($_->{name}) . " $_->{type}" } @$cols);
107 print $sqlfh " INHERITS (${base_table})" if $base_table;
111 print $sqlfh "\\COPY $pg_table (" . join(", ", map { $column_prefix . lc($_->{name}) } @$cols) . ") FROM '$out'\n";
115 sub normalize_value_for_tsv {
119 $val =~ s/\0//g; # FIXME: not dealing with BLOBs for now
120 $val =~ s/[\b]/\\b/g;
127 return decode($src_charset, $val);
138 my $sth_cols = $dbh->prepare('
139 SELECT column_name, data_type, data_precision, data_scale, data_length, nullable
140 FROM user_tab_columns WHERE table_name = ? ORDER BY column_id
142 $sth_cols->execute($table);
143 my @cols = map { { name => $_->{COLUMN_NAME}, type => get_pg_column_type($_) } }
144 @{ $sth_cols->fetchall_arrayref({}) };
149 sub get_pg_column_type {
150 my $column_def = shift;
152 if ($column_def->{DATA_TYPE} =~ /VARCHAR/) {
154 } elsif ($column_def->{DATA_TYPE} eq 'DATE') {
156 } elsif ($column_def->{DATA_TYPE} eq 'NUMBER') {
157 if (!defined($column_def->{DATA_SCALE}) || $column_def->{DATA_SCALE} == 0) {
160 $type = "NUMERIC($column_def->{DATA_PRECISION},$column_def->{DATA_SCALE})";
162 } elsif ($column_def->{DATA_TYPE} eq 'CHAR') {
163 $type = "CHAR($column_def->{DATA_LENGTH})";
166 $type .= " NOT NULL" if $column_def->{NULLABLE} eq 'N';