Re: How to convert Oracle database to Postgresql.

Hi Wayne,
Yes.  I would not mind getting a copy of the code.  I wrote a java
program to copy the data via JDBC and it works but it is rather slow.  I
am not using triggers either but I a use lots of blobs (I converted them
into bytea also)
Regards

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wayne Armstrong
Sent: Wednesday, June 18, 2003 8:34 AM
To: Carlos; pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.


** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed, 18
Jun 2003 08:12:00 -0400 Hi Carlos,  We've just been through this
recently.  We used db2look to get the ddl for the database, ran some
editing scripts over it (just ex) and fed that into postgres.  To load
data from the db2 database, I wrote a smalltalk program that for each
table, read from the db2 dtabase and inserted into the postgres
database. The smalltalk needs no prior knowledge of the table layouts to
do that- queries syscat.tables in the db2 database to get a list of
tables etc. You are welcome to the smalltalk executable (and source if
you want it (it is not really production code though :) )

We used bytea in place of blob.
Triggers we did manually (we are not big users of triggers).

All in all it was pretty easy. Places we came unstuck were on views
using the "with temp" syntax, and some recursive sql which we rewrote
using pgplsql and temp tables. Also Db2 will hapilly store 24:00:00 as
well as 00:00:00 for midnight. It's worth updateing any occurennces of
24:00:00 to 00:00:00 before you convert.


Regards,
Wayne Armstrong,
Bacchus Management Systems.

> Is there a script or method to convert a DB2 database also? Thanks
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Justin Clift
> Sent: Wednesday, June 18, 2003 7:02 AM
> To: Mike Mascari
> Cc: Ursula Lee; pgsql-general@postgresql.org; gilles Darold
> Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
>
>
> Hi Ursula,
>
> That script only works for Oracle 8.x though, and needs modification
> for Oracle 9i.
>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> Mike Mascari wrote:
> > Ursula Lee wrote:
> >
> >
> >>Dear all,
> >>
> >>Is there any script/tool I can use to convert Oracle database to
> >>Postgresql?
> >
> >
> > The /contrib directory of the distribution contains an ora2pg script
> > used to generate a conversion. I've never used it though.
> >
> > Mike Mascari
> > mascarm@mascari.com
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> >       subscribe-nomail command to majordomo@postgresql.org so that
> your
> >       message can get through to the mailing list cleanly
>
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
> - Indira Gandhi
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)