Re: dump data from postgreSQL to Oracle

"Laurenz Albe" <invite_at_spam.to.invalid> schreef in bericht news:1159856852.261694_at_proxy.dienste.wien.at...
> chunji08_at_gmail.com wrote:
>> We have an existing project that is running by perl/cgi-bin, and
>> postgreSQL is the backend DB server. And now we are trying to rewrite
>> this project using java, and Oracle will be the backend db.
>
> Good luck.
>
>> We started
>> the new one months ago, and now we need some real-time data to do some
>> integration test. So the question is how to move some data in existing
>> PostgreSQL table to Oracle table.
>>
>> I never did such think before, the only way I can think of is to dump
>> the data from PostgresSQL table to a file and have Oracle reads it ?
>> But what happen there is schema change between either other, or some
>> datatype mis-match, etc .. ?
>> I wand to know what is the Professional way to do such kind of work.
>> Thanks.
>
> I don't know about Professional with a capital P, but I see two options:
>
> - Use pg_dump to extract the PostgreSQL database as SQL commands,
> modify the commands with a program or an editor to Oracle syntax
> and run the result as SQL script.
>
> - Use COPY to extract the data as CVS file and load it with SQL*Loader
> into Oracle.
>
> Yours,
> Laurenz Albe

There are tools to do the work for you, but they are not always performing very well, see http://www.delmolinos.net/blog/?cat=3 (look for That'll teach me)
As the author of this article states, HS (Heterogenous Services) might help you. With HS you can create a database link between Oracle and a Postgres database, and PG will behave like an Oracle DB.

Robbert van der Hoorn
OSA it BV
The Netherlands Received on Tue Oct 03 2006 - 14:23:58 CDT