Re: from postgres to oracle

On Thu, 24 Oct 2002, Carlos Sousa wrote:

> can some one tell how to transfer the contents from a postgres database into
> a oracle database

Carlos,

I use pg_dump to create full CREATE strings, with no double quotes.

pg_dump -s -n -D -x dbname > dbname.sql

Then I use pg_dump to create full INSERT strings, with no double quotes.

pg_dump -a -n -D -x dbname > dbname.df

Then I edit the dbname.sql file to remove things oracle objects to
like TEXT datatypes (change to whatever you prefer, LONG, VARCHAR2(4000),
etc.) SEQUENCE syntax has to be modified slightly, GRANTS (removed with
the -x flag) should not exist, etc.

I prepend my usual Oracle stuff (SET NLS_DATE_FORMAT = 'whatever you used with
Pg';, SET SCAN OFF; SPOOL load.log;, etc) to the file and then test it.
With sql*plus as the loader you'll want to stick an EXIT; at the EOF if
you run the script using indirection (sqlplus user/pass @dbname.sql)

HTH

I have examples, if you want em.
-----------------------------------------------------------------------
Thomas Good e-mail: tomg(at)sqlclinic(dot)net
Programmer/Analyst phone: (+1) 718.818.5528
Residential Services fax: (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359
-- --
SQL Clinic - An Open Source Clinical Record www.sqlclinic.net
------------------------------------------------------------------------