Not knowing how to create exp/expdp files from Postgresql, we used a combination of pg_dump and sqlloader instead. The nice thing about it was that the whole thing could be wrapped up in a bash script and tucked in cron (this was on Solaris and/or Linux).
It looked something like:
The migrate.par file:
userid=<USERNAME>/<PASSWORD>@<DATABASE>
The driver script:
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
my $par_file = 'migrate.par';
# List of tables to copy
my @tables = qw(
schema_name.table_name_1
schema_name.table_name_2
schema_name.table_name_3
);
# Disable oracle constraints and triggers (if needed)
`sqlplus <schema_name>/<password>\@<database> \@disable_constraints.sql`;
# Copy the tables
foreach my $table (@tables) {
`perl ./pg_to_sqlloader.pl $table`;
sqlload($table, $par_file);
}
# Enable oracle triggers and constraints (if needed)
`sqlplus <schema_name>/<password>\@<database> \@enable_constraints.sql`;
sub sqlload {
my ($table, $par_file) = @_;
my $ctl_file = "${table}.ctl";
my $log_file = "${table}.log";
warn(" Loading $ctl_file...");
system(
"sqlldr rows=500 readsize=4000000 bindsize=4000000 control=$ctl_file log=$log_file parfile=$par_file"
);
}
__END__
The pg_2_sqlloader.pl script:
#!/usr/bin/env perl
########################################################################
=head1 NAME
pg_to_sqlloader.pl
=head1 SYNOPSIS
B<pg_to_sqlloader.pl> <list of tables to export>.
=head1 DESCRIPTION
Extracts data from a postgresql table and creates the necessary files
for importing (via sqlloader) to oracle.
=head1 ASSERTIONS
- The schema name matches.
- The table names match; over-long table names got properly shortened.
- The column names match; over-long column names got properly shortened.
- The case of the schema/table/column names is the default for the
respective databases.
- Boolean data types are translated to char(1) {Y, N}
=head1 AUTHOR
gsiems
=head1 HISTORY
2010.09.30 Created.
=cut
########################################################################
# SETUP:
use strict;
use warnings FATAL => 'all';
########################################################################
# MAIN:
use Data::Dumper;
my $pg_host = 'localhost';
my $pg_port = '5432';
my $pg_user = 'postgres';
my $pg_db = 'database_name';
foreach my $arg (@ARGV) {
my ($schema, $tablename) = parse_tablename($arg);
warn "Extracting $tablename data...";
export_pg($schema, $tablename);
my @table_meta = get_pg_meta($schema, $tablename);
warn " Creating control file...";
make_ora_ctl($schema, $tablename, \@table_meta);
}
sub parse_tablename {
my $arg = shift;
my ($schema, $tablename);
if ($arg =~ m/\./) {
($schema, $tablename) = split /\./, $arg;
} else {
$tablename = $arg;
$schema = 'public';
}
return ($schema, $tablename);
}
sub pg_export_filename {
my ($schema, $tablename) = @_;
return "${schema}.${tablename}.out";
}
sub export_pg {
my ($schema, $tablename) = @_;
my $export_file = pg_export_filename($schema, $tablename);
my $cmd =
"psql --host=$pg_host --port=$pg_port --username=$pg_user "
. "--dbname=$pg_db --tuples-only --no-align "
. "--command=\"select * from $schema.$tablename;\" "
. " > $export_file";
}
sub get_pg_meta {
my ($schema, $tablename) = @_;
my $cmd = "psql --host=$pg_host --port=$pg_port --username=$pg_user --dbname=$pg_db"
. " --tuples-only --no-align --command=\"\\d $schema.$tablename;\" ";
my @table_meta = grep { $_ =~ m/\|/ } `$cmd`;
chomp @table_meta;
return @table_meta;
}
sub make_ora_ctl {
my ($schema, $tablename, $meta_ref) = @_;
my $ctl_filename = "${schema}.${tablename}.ctl";
my $export_file = pg_export_filename($schema, $tablename);
my $imp_tablename = shorten_name($tablename);
my $ctl = << "EOT";
LOAD DATA
INFILE '$export_file'
BADFILE '${schema}3_from_2.$imp_tablename.bad'
DISCARDFILE '${schema}3_from_2.$imp_tablename.dsc'
TRUNCATE INTO TABLE ${schema}3_from_2.$imp_tablename
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EOT
my @cols = ();
foreach my $col_meta (@{$meta_ref}) {
my ($colname, $datatype, $modifiers) = split /\|/, $col_meta;
my $column_name = shorten_name($colname);
my $data_type = 'CHAR'; # set the default
# translate postgres datatypes and domains to something oracle can deal with:
my $data_trans = '';
if ($datatype =~ m/^(date|timestamp)/) {
$data_type = 'DATE "YYYY-MM-DD HH24:MI:SS"';
} elsif ($datatype eq 'd_text_comment') {
$data_type = 'CHAR(1024)';
}
elsif ($datatype =~ m/^(character varying|varchar)/) {
my ($data_length) = $datatype =~ m/\((\d+)\)/;
if ($data_length && $data_length >= 500) {
$data_type = "CHAR($data_length)";
}
} elsif ($datatype =~ m/^(d_flag|boolean)/) {
$data_type = "CHAR ";
$data_trans = " \"translate(upper(:$column_name), 'TF','YN')\" ";
}
my $nullif = ($modifiers =~ m/not null/) ? '' : "NULLIF ($column_name = blanks)";
my $line = sprintf(" %-32s%-30s%-70s", $column_name, $data_type, $nullif . $data_trans);
$line =~ s/ +$//;
push @cols, $line;
}
$ctl .= join ",\n", @cols;
$ctl .= "\n)\n";
print $ctl;
my $OUT;
open($OUT, '>', $ctl_filename) || die "could not open $ctl_filename for output $!\n";
print $OUT $ctl;
close $OUT;
}
sub shorten_name {
my $name = shift;
return $name if (length $name <= 30);
# For those names that exceed the oracle length limit...
my @abbreviations = (
'control,ctrl', 'process,proc', 'material,matl',
'capacity,cap', 'group,grp',
);
my $new_name = $name;
foreach my $abbr (@abbreviations) {
my ($f, $r) = split ',', $abbr;
$new_name =~ s/$f/$r/g;
last if (length($new_name) <= 30);
}
if (length $new_name > 30) {
warn "$new_name (" . length $new_name . ") is still too long! Was $name\n";
}
return $new_name;
}
__END__
Updated to add:
The query for creating the enable/disable constraints scripts:
-- disable script
SELECT 'alter table '
|| a.owner || '.' || a.table_name
|| ' disable constraint ' || a.constraint_name
|| ';' AS "-- Disable Constraints"
FROM dba_constraints a
JOIN dba_constraints b
ON a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner
WHERE a.constraint_type = 'R'
AND b.status = 'ENABLED'
AND b.owner = UPPER ( '&table_owner' )
-- AND b.table_name = UPPER ( '&table_name' )
;
-- re-enable script
SELECT 'alter table '
|| a.owner || '.' || a.table_name
|| ' enable constraint ' || a.constraint_name
|| ';' AS "-- Enable Constraints"
FROM dba_constraints a
JOIN dba_constraints b
ON a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner
WHERE a.constraint_type = 'R'
AND b.status = 'ENABLED'
AND b.owner = UPPER ( '&table_owner' )
-- AND b.table_name = UPPER ( '&table_name' )
;
