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.
28 my $host = 'localhost';
29 my $sid = $ENV{ORACLE_SID};
37 my $column_prefix = '';
40 my $result = GetOptions(
48 'pg-table=s' => \$pg_table,
49 'column-prefix=s' => \$column_prefix,
50 'inherits-from=s' => \$base_table,
51 'help' => \$show_help,
54 if ($show_help || !$result || !$out || !$sql || !$user || !$pw || !$table || !$pg_table) {
56 $0: dump contents of Oracle table to file for loading into PostgreSQL
59 [--sid oracle_sid] [--host oracle_host] --user oracle_user --pw oracle_password \\
60 --table oracle_table_name \\
61 --pg-table destination_pg_table_name \\
62 --out output_tsv_file --sql output_table_create_sql_file \\
63 [--column-prefix column_prefix] [--inherits-from base_pg_table] [--help]
69 my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", $user, $pw) or croak "Cannot connect to the database";
70 $dbh->do("ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'");
72 open my $outfh, '>', $out or croak "Cannot open output file $out: $!\n";
73 binmode $outfh, ':raw';
74 open my $sqlfh, '>', $sql or croak "Cannot open output file $sql: $!\n";
75 binmode $sqlfh, ':raw';
77 export_table(uc $table, $outfh, $sqlfh, $out);
89 my $cols = get_columns($table);
90 my $query = 'SELECT ' . join(', ', map { $_->{name} } @$cols) . " FROM $table";
91 my $sth = $dbh->prepare($query);
93 while (my $row = $sth->fetchrow_arrayref()) {
94 my @data = map { normalize_value_for_tsv($_) } @$row;
95 my $str = join("\t", @data);
101 print $sqlfh "CREATE TABLE $pg_table (\n";
102 print $sqlfh join(",\n", map { $column_prefix . lc($_->{name}) . " $_->{type}" } @$cols);
104 print $sqlfh " INHERITS (${base_table})" if $base_table;
108 print $sqlfh "\\COPY $pg_table (" . join(", ", map { $column_prefix . lc($_->{name}) } @$cols) . ") FROM '$out'\n";
112 sub normalize_value_for_tsv {
116 $val =~ s/\0//g; # FIXME: not dealing with BLOBs for now
117 $val =~ s/[\b]/\\b/g;
131 my $sth_cols = $dbh->prepare('
132 SELECT column_name, data_type, data_precision, data_scale, data_length, nullable
133 FROM user_tab_columns WHERE table_name = ? ORDER BY column_id
135 $sth_cols->execute($table);
136 my @cols = map { { name => $_->{COLUMN_NAME}, type => get_pg_column_type($_) } }
137 @{ $sth_cols->fetchall_arrayref({}) };
142 sub get_pg_column_type {
143 my $column_def = shift;
145 if ($column_def->{DATA_TYPE} =~ /VARCHAR/) {
147 } elsif ($column_def->{DATA_TYPE} eq 'DATE') {
149 } elsif ($column_def->{DATA_TYPE} eq 'NUMBER') {
150 if (!defined($column_def->{DATA_SCALE}) || $column_def->{DATA_SCALE} == 0) {
153 $type = "NUMERIC($column_def->{DATA_PRECISION},$column_def->{DATA_SCALE})";
155 } elsif ($column_def->{DATA_TYPE} eq 'CHAR') {
156 $type = "CHAR($column_def->{DATA_LENGTH})";
159 $type .= " NOT NULL" if $column_def->{NULLABLE} eq 'N';