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|improve this question

migrated from stackoverflow.com Mar 22 '12 at 12:29

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

If by "Oracle dump file" you are talking about the files that are generated by the Oracle exp or expdp utilities, you probably can't. Oracle's Export and Export Data Pump tools generate files in a proprietary binary format that is not documented. I suppose you could spend a ridiculous number of hours trying to reverse engineer the file format but that would not be particularly practical.

On the other hand, since a PostgreSQL dump file is just a set of SQL commands, you probably wouldn't want to convert it into an Oracle dump file. If your goal is to port the schema from PostgreSQL to Oracle, Oracle's SQL Developer includes what used to be the Oracle Migration Workbench. You should be able to use that tool to help you port the schema definition to Oracle. The dialect of SQL that PostgreSQL supports is also very similar to the dialect that Oracle supports so you can probably just run the PostgreSQL dump file against an Oracle database and most of the statements will likely just work.

share|improve this answer
    
Thanks Justin . I have large amount of data in the PostgreSQL and need to migrate in the Oracle .what is the best solution for it .When i try using custom code it took almost 7 days to complete it .But I need to migrate the data with in hours .How about if I dump the data in to flat files table vise and Import in the Oracle .Could you please give me your suggestions about this approach. Thanks –  user534817 Mar 22 '12 at 4:05
1  
@user534817 - How much data are we talking about? How many hours do you have? Can you connect from the server with the Oracle database to the server with the PostgreSQL database? How much bandwidth do you have between the two servers? –  Justin Cave Mar 22 '12 at 4:14
    
Thanks Justin.We have 38 GB of data , in 229 tables and I have 2 hours to complete it.I dont have direct connectivity from Oracle database server to the server with postgresSQL .The application which is using PostgresSQL was managed by other company and we are building in house project for the same app with Oracle Database. Please suggest some approch .Thanks –  user534817 Mar 23 '12 at 1:57
add comment

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|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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