Oracle to Postgres conversion ...

John Shott shott at snf.stanford.edu
Sat Oct 13 10:45:11 PDT 2001


Bill and Mike:

I'm beginning to look at the matter of converting our database from Oracle to
Postges.

The website:
http://techdocs.postgresql.org/
has a set of references related to converting about any database (including
Oracle) into postres.

Of of the links there is:
http://www.samse.fr/GPL/ora2pg/ora2pg.html

This describes a Perl module called Ora2Pg that seems to be able to covert an
Oracle database schema into a Postgres verion.  It can spit out a sql table to
do this.  While I suspect that we were originally thinking that we may simply
take the *.sql files that generated our database ... it would appear that this
may offer us the opportunity to use this tool to extract what we have in
Oracle and re-create it (more or less) in Postgres.  At the above website,
their first example of this is:

        BEGIN {
                $ENV{ORACLE_HOME} = '/usr/local/oracle/oracle816';
        }

        use strict;

        use Ora2Pg;

        # Init the database connection
        my $dbsrc = 'dbi:Oracle:host=testdb.samse.fr;sid=TEST;port=1521';
        my $dbuser = 'system';
        my $dbpwd = 'manager';

        # Create an instance of the Ora2Pg perl module
        my $schema = new Ora2Pg (
                datasource => $dbsrc,           # Database DBD datasource
                user => $dbuser,                # Database user
                password => $dbpwd,             # Database password
        );

        # Create the POSTGRESQL representation of all objects in the database
        $schema->export_schema("output.sql");

        exit(0);

It appears that they can extract just about everything:
table extraction ... the structure of the table,
data extraction
functions and procedures ..
grant information.

If I can get postgres ready to run on my linux box, this might be a good place
to test some of this stuff ....

Talk to you later,

John



More information about the coral mailing list