Re: transfer data from oracle to postgres

s wrote:

> Is there an easy way to transfer DATA from an oracle database to a
> postgres database (tables are already created)?  As far as I can tell
> the oracle export creates a binary file.  I could write a script, but
> it would be a bit tedious.  I imagine it has already been done?
>
> I searched the archives and docs and the web, but the archives are
> coming up quite right on my browser and I didn't find any good
> suggestions.


There is the ora2pg perl script. But I chose to do this manually.
It's easy to do some Oracle metadata queries and create statements
that suck the entire table. You could do that even in Oracle SQL*Plus
if it wasn't so stupid about trying to format an ASCII table, etc.
In the end the delimiter-escape problem forced me to just make a
quick Java/JDBC program. You can stream the reocrds right into a
pgsql COPY FROM command. Streaming is useful if your tables are
BIG (mine are.) I also put some counter in the middle that shows
me the progress. I noticed that PostgreSQL could pump data in as
fast as I could read it from the Oracle server. 300 to 1500 records
per second was the normal range.

regards
-Gunther




--
Gunther Schadow, M.D., Ph.D.                    gschadow@regenstrief.org
Medical Information Scientist      Regenstrief Institute for Health Care
Adjunct Assistant Professor        Indiana University School of Medicine
tel:1(317)630-7960                         http://aurora.regenstrief.org