Re: How to convert Oracle database to Postgresql.

Hi Ron,
Probably ....  The java program I wrote works by copying the tables via
JDBC so it does not produce any text files and relies on the capabilies
of JDBC to treat SQL types without having to specify them.  It is
similar to methods published to copy databases in the server.  In fact
it copied a BLOB from DB2 into a bytea of PostgreSQL without my having
to deal with SQL types (I tried a program that copy tables via ODBC but
it failed everytime that a BLOB had to be copied).

I imagine that one could output data from tables into text files by
outputing the data as objects.  In this way one may not have to deal
with the different SQL types.  I am not sure about other aspects of the
database such as triggers

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson
Sent: Wednesday, June 18, 2003 10:12 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.


On Wed, 2003-06-18 at 07:49, Carlos Oliva wrote:
> 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.

Wouldn't it be faster to do host-based conversions to tab-delimited
files that can be loaded into postgres via COPY?

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           | "Oh, great

| altar of passive entertainment, bestow upon me |  thy discordant
| images at such speed as to render linear  |
|  thought impossible" (Calvin, regarding TV)               |
+-----------------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html