Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I convert postgresql dump files to oracle dump files? If anyone has experience in this, or knows of an open source tool I can use, please share it with me.

share

migration rejected from stackoverflow.com Jan 27 at 19:29

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

put on hold as off-topic by swasheck, RolandoMySQLDBA, Phil, Mikael Eriksson, RLF Jan 27 at 19:29

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – swasheck, RolandoMySQLDBA, Phil, Mikael Eriksson, RLF
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

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' ) 
;
share
    
thanks gsiems.can you please share the disable_constraints.sql and enable_constraints.sql .please I will try with your scripts . –  user534817 Mar 27 '12 at 1:43
    
@user534817 -- I've added sql for the scripts. –  gsiems Mar 27 '12 at 12:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.