Shaunn Johnson wrote: > At the risk of sounding silly, does anyone know > of a way to export data from PostgreSQL to > Oracle via perl featuring DBI? > > Background: > I'm running PostgreSQL 7.2.1 on RedHat Linux 7.2 > and I have Oracle 9i on the same box. PostgreSQL > is dying and the tool to dump the data (pg_dump) > no longer works (actually, it's due to a severe > problem with the PostgreSQL database ...) and > the best shot I have now are a few items: > > * use MS Access to migrate (shoot me now!) > * use Perl > * use copy on PostgreSQL and sqlldr for Oracle > (and then figure out how to automatically make > schema/ table DDL for all 1500 tables) > > All of the options above have issues, however, I figured > that using Perl (DBI) would be better because I > could open up a connection between both databases > at the same time and just pipe the information > from one to the other. > > For that to happen, I need to figure out how to > keep the data (from Pg) and open a new session > with Oracle and I don't know how to do that. I > can open up one or the other database and put > data into a file ... > > Unless there is a conversion / migration tool that I > don't know of, can someone point me to an example of > directly moving data from one database to another > via DBI? > You plan a very serious project (1500 tables!). This forum is not right place to discuss all aspects of the project. But you asked and I give you just generic approach for the tasks.
Firstly you need to reengineer PostgreSQL database objects into good modeling tool (e.g. Erwin, or, I recommend Oracle Designer, if there are funds available, and skills as well). Next step is to (manually or otherwise) massage logical structures to satisfy Oracle formats. Particular problems could be with object IDs and extensions differently implemented in these two architectures (if, at all, object-relational structures were used) . After that to forward engineer logical objects into Oracle schema objects. In regard to data transfer, probably is good to use native PostgreSQL tools (if available), or relevant perl APIs/modules, to transfer data into OS files. Then, using some Oracle native tools, e.g. Sql Loader (sqlldr) to import data into Oracle. This phase must be carefully planned in regard to existing set of constraints. If you try to disable constraints beforehand, than transfer can be done smoothly. But afterwards system could face severe problems very hard to fix. Regards Pavle -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
