pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL to Oracle
10+ messages / 7 participants
[nested] [flat]
* PostgreSQL to Oracle
@ 2007-03-09 17:54 Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-03-09 18:32 ` Re: [SQL] PostgreSQL to Oracle Jonah H. Harris <jonah.harris@gmail.com>
2007-03-09 18:40 ` Re: PostgreSQL to Oracle Martin Gainty <mgainty@hotmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Ezequias Rodrigues da Rocha @ 2007-03-09 17:54 UTC (permalink / raw)
To: pgsql general <pgsql-general@postgresql.org>; pgsql-sql
Hi list,
Is it a simple action to convert a database from PostgreSQL to Oracle ?
I mean a simple database with
33 tables
8 functions
31 sequencies
2 triggers
1 type
3 views
Has someone any idea ?
--
Ezequias Rodrigues da Rocha
http://ezequiasrocha.blogspot.com/
use Mozilla Firefox:http://br.mozdev.org/firefox/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] PostgreSQL to Oracle
2007-03-09 17:54 PostgreSQL to Oracle Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
@ 2007-03-09 18:32 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-03-09 19:32 ` Re: [SQL] PostgreSQL to Oracle Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-03-15 20:20 ` Re: [SQL] PostgreSQL to Oracle Robert Treat <xzilla@users.sourceforge.net>
1 sibling, 2 replies; 10+ messages in thread
From: Jonah H. Harris @ 2007-03-09 18:32 UTC (permalink / raw)
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; +Cc: pgsql general <pgsql-general@postgresql.org>; pgsql-sql
On 3/9/07, Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com> wrote:
> Is it a simple action to convert a database from PostgreSQL to Oracle ?
Yes, relatively.
> Has someone any idea ?
There's a couple ways to do this, but I'd recommend first using
pg_dump to export schema only.
Your functions and triggers would need to be rewritten, but assuming
they're in PL/pgSQL, it's a fairly trivial task to translate them into
PL/SQL.
As far as the views and sequences are concerned, pull them out of the
pg_dump export and re-run them in TOAD, SQL*Plus, or your favorite
tool.
As far as the type goes, I'm not quite sure what you're doing with it
or how it's used, but it should also be easy to migrate.
To copy the data and table definitions, I'd use a database link (on
the Oracle side) with hsodbc connecting to your PostgreSQL system via
ODBC.
Now that my advice is done with, could you explain why you need to
move to Oracle from PostgreSQL?
--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] PostgreSQL to Oracle
2007-03-09 17:54 PostgreSQL to Oracle Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-03-09 18:32 ` Re: [SQL] PostgreSQL to Oracle Jonah H. Harris <jonah.harris@gmail.com>
@ 2007-03-09 19:32 ` Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Ezequias Rodrigues da Rocha @ 2007-03-09 19:32 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: pgsql general <pgsql-general@postgresql.org>; pgsql-sql
Thank you Jonah,
That isn't a decision taken but I will need to argue with the new team of my
new company. I can't see why but I will see how the things occurs.
Thank you again
Ezequias
2007/3/9, Jonah H. Harris <jonah.harris@gmail.com>:
>
> On 3/9/07, Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com> wrote:
> > Is it a simple action to convert a database from PostgreSQL to Oracle ?
>
> Yes, relatively.
>
> > Has someone any idea ?
>
> There's a couple ways to do this, but I'd recommend first using
> pg_dump to export schema only.
>
> Your functions and triggers would need to be rewritten, but assuming
> they're in PL/pgSQL, it's a fairly trivial task to translate them into
> PL/SQL.
>
> As far as the views and sequences are concerned, pull them out of the
> pg_dump export and re-run them in TOAD, SQL*Plus, or your favorite
> tool.
>
> As far as the type goes, I'm not quite sure what you're doing with it
> or how it's used, but it should also be easy to migrate.
>
> To copy the data and table definitions, I'd use a database link (on
> the Oracle side) with hsodbc connecting to your PostgreSQL system via
> ODBC.
>
> Now that my advice is done with, could you explain why you need to
> move to Oracle from PostgreSQL?
>
> --
> Jonah H. Harris, Software Architect | phone: 732.331.1324
> EnterpriseDB Corporation | fax: 732.331.1301
> 33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
> Iselin, New Jersey 08830 | http://www.enterprisedb.com/
>
--
Ezequias Rodrigues da Rocha
http://ezequiasrocha.blogspot.com/
use Mozilla Firefox:http://br.mozdev.org/firefox/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] PostgreSQL to Oracle
2007-03-09 17:54 PostgreSQL to Oracle Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-03-09 18:32 ` Re: [SQL] PostgreSQL to Oracle Jonah H. Harris <jonah.harris@gmail.com>
@ 2007-03-15 20:20 ` Robert Treat <xzilla@users.sourceforge.net>
2007-03-15 20:47 ` Re: [SQL] PostgreSQL to Oracle Jonah H. Harris <jonah.harris@gmail.com>
2007-03-16 17:18 ` Re: [SQL] PostgreSQL to Oracle Reece Hart <reece@harts.net>
1 sibling, 2 replies; 10+ messages in thread
From: Robert Treat @ 2007-03-15 20:20 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: Jonah H. Harris <jonah.harris@gmail.com>; Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
On Friday 09 March 2007 13:32, Jonah H. Harris wrote:
> To copy the data and table definitions, I'd use a database link (on
> the Oracle side) with hsodbc connecting to your PostgreSQL system via
> ODBC.
>
Do you find this works well? I've used it from some older Oracle instances
connecting back into PostgreSQL and the results I had have been flakey at
best.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] PostgreSQL to Oracle
2007-03-09 17:54 PostgreSQL to Oracle Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-03-09 18:32 ` Re: [SQL] PostgreSQL to Oracle Jonah H. Harris <jonah.harris@gmail.com>
2007-03-15 20:20 ` Re: [SQL] PostgreSQL to Oracle Robert Treat <xzilla@users.sourceforge.net>
@ 2007-03-15 20:47 ` Jonah H. Harris <jonah.harris@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Jonah H. Harris @ 2007-03-15 20:47 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-general@postgresql.org, "Ezequias Rodrigues da Rocha" <ezequias.rocha@gmail.com>
On 3/15/07, Robert Treat <xzilla@users.sourceforge.net> wrote:
> Do you find this works well? I've used it from some older Oracle instances
> connecting back into PostgreSQL and the results I had have been flakey at
> best.
It really just depends on the data types in use... but I've never
really had anything I'd call, "flakey" happen this way.
--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] PostgreSQL to Oracle
2007-03-09 17:54 PostgreSQL to Oracle Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-03-09 18:32 ` Re: [SQL] PostgreSQL to Oracle Jonah H. Harris <jonah.harris@gmail.com>
2007-03-15 20:20 ` Re: [SQL] PostgreSQL to Oracle Robert Treat <xzilla@users.sourceforge.net>
@ 2007-03-16 17:18 ` Reece Hart <reece@harts.net>
2007-03-16 17:44 ` Re: [SQL] PostgreSQL to Oracle David Fetter <david@fetter.org>
1 sibling, 1 reply; 10+ messages in thread
From: Reece Hart @ 2007-03-16 17:18 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-general@postgresql.org, "Jonah H. Harris" <jonah.harris@gmail.com>; Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
On Thu, 2007-03-15 at 16:20 -0400, Robert Treat wrote:
> Do you find [HSODBC] works well? I've used it from some older Oracle
> instances connecting back into PostgreSQL and the results I had have
> been flakey at best.
For us too. We tried in early 2006 with Oracle 8 via hsodbc to
postgresql 7.4.6 and had several problems. From what I remember:
- Oracle complained about SQLError when using unixODBC 3.0. This
necessitated using 2.5.
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbpgen9/00000291.htm
- We had type conversion problems. I can't remember many details of
this, but I *think* they may have been text/varchar and timestamp/date.
We don't use blobs.
- It took our admins a fair bit of time to get working. It probably
didn't help that Oracle was running on AIX.
- The real nail in the coffin seemed to be that there was no way to push
predicates to the remote side. This mean that a remote relation was
materialized locally in order to join with local data and that we'd
never get reasonable performance for cross-database joins (which,
perhaps, it's not designed to do).
I'm particularly curious about this last problem. Without pushing
predicates to the remote side, it's hard for me to imagine that HSODBC
would be terribly useful for anything but building a local warehouse.
This seems so odd that I think I must be wrong, but I couldn't find any
documentation that to support or discourage cross-database joins. Can
someone out there enlighten me? What's the intent for HSODBC and what
are its limitations? Can one access remote server side functions via
HSODBC (eg, sqlplus> select col1,plpgsqlfx(col1) from remotetable)?
Thanks,
Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] PostgreSQL to Oracle
2007-03-09 17:54 PostgreSQL to Oracle Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-03-09 18:32 ` Re: [SQL] PostgreSQL to Oracle Jonah H. Harris <jonah.harris@gmail.com>
2007-03-15 20:20 ` Re: [SQL] PostgreSQL to Oracle Robert Treat <xzilla@users.sourceforge.net>
2007-03-16 17:18 ` Re: [SQL] PostgreSQL to Oracle Reece Hart <reece@harts.net>
@ 2007-03-16 17:44 ` David Fetter <david@fetter.org>
2007-03-16 18:20 ` Re: [SQL] PostgreSQL to Oracle Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: David Fetter @ 2007-03-16 17:44 UTC (permalink / raw)
To: Reece Hart <reece@harts.net>; +Cc: Robert Treat <xzilla@users.sourceforge.net>; pgsql-general@postgresql.org, "Jonah H. Harris" <jonah.harris@gmail.com>; Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
On Fri, Mar 16, 2007 at 10:18:56AM -0700, Reece Hart wrote:
> On Thu, 2007-03-15 at 16:20 -0400, Robert Treat wrote:
> > Do you find [HSODBC] works well? I've used it from some older
> > Oracle instances connecting back into PostgreSQL and the results I
> > had have been flakey at best.
>
> - The real nail in the coffin seemed to be that there was no way to
> push predicates to the remote side. This mean that a remote relation
> was materialized locally in order to join with local data and that
> we'd never get reasonable performance for cross-database joins
> (which, perhaps, it's not designed to do).
DBI-Link has a way to push predicates to the remote side, but you have
to do it manually because DBI-Link is a user-space application, i.e.
it doesn't have access to PostgreSQL's planner.
Let me know if you have questions on how this works :)
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] PostgreSQL to Oracle
2007-03-09 17:54 PostgreSQL to Oracle Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-03-09 18:32 ` Re: [SQL] PostgreSQL to Oracle Jonah H. Harris <jonah.harris@gmail.com>
2007-03-15 20:20 ` Re: [SQL] PostgreSQL to Oracle Robert Treat <xzilla@users.sourceforge.net>
2007-03-16 17:18 ` Re: [SQL] PostgreSQL to Oracle Reece Hart <reece@harts.net>
2007-03-16 17:44 ` Re: [SQL] PostgreSQL to Oracle David Fetter <david@fetter.org>
@ 2007-03-16 18:20 ` Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jonah H. Harris @ 2007-03-16 18:20 UTC (permalink / raw)
To: David Fetter <david@fetter.org>; +Cc: Reece Hart <reece@harts.net>; Robert Treat <xzilla@users.sourceforge.net>; pgsql-general@postgresql.org, "Ezequias Rodrigues da Rocha" <ezequias.rocha@gmail.com>
On 3/16/07, David Fetter <david@fetter.org> wrote:
> DBI-Link has a way to push predicates to the remote side, ...
As Ezequias asked about migrating an application, I'm not quite sure
why we're discussing this. Using HSODBC to move data permanently is
quite good assuming you have no data type issues.
--
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 3rd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL to Oracle
2007-03-09 17:54 PostgreSQL to Oracle Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
@ 2007-03-09 18:40 ` Martin Gainty <mgainty@hotmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Martin Gainty @ 2007-03-09 18:40 UTC (permalink / raw)
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; pgsql general <pgsql-general@postgresql.org>
Ezequias-
first you will need to pg_dump everything.. schema..tables..functions then your Data into a text format which has no whitespace characters
There are 2 bulk loaders available from Oracle
1)Brand new DataPump
2)Tried and true sqlldr (which I recommend)
I would highly recommend reading and understanding the Extract / Transform /Load document located at
http://www.oracle.com/technology/obe/obe10gdb/bidw/etl2/etl2.htm
Also triple check the extents you are allocating for the tablespace for tables to be inserted into that tablespace will guaranteed to be sufficient size
(If you dont know the size of a block ask..as this isnt something you can fix later..unless you want to spend a 3day weekend doing a rollback..)
Become familiar with LOAD DATA INFILE command links available from either Stanford (or Oracle)
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-load.html
Be mindful that Oracle DB is used by ALL of the major players
so inserting nulls which could violate any constraint (constraints being unique or foreign key) are forbidden
In fact inserting nulls into any column is discouraged as it may violate the relationship of dependent table records to primary table
If you have time and or diskspace constraint you may want to look at Direct Path Loading which creates preformatted data blocks
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c21dlins.htm
Caveat emptor with this option as the logger is turned off and if there are any burps along the way you wont know which record it burped on
Feel free to ping me if you have any questions/
Saludos Cordiales,
Martin --
---------------------------------------------------------------------------
This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this communication is strictly prohibited.
---------------------------------------------------------------------------
Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire indiqué et peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce document, nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire.
----- Original Message -----
From: Ezequias Rodrigues da Rocha
To: pgsql general ; PostgreSQL
Sent: Friday, March 09, 2007 12:54 PM
Subject: [GENERAL] PostgreSQL to Oracle
Hi list,
Is it a simple action to convert a database from PostgreSQL to Oracle ?
I mean a simple database with
33 tables
8 functions
31 sequencies
2 triggers
1 type
3 views
Has someone any idea ?
--
Ezequias Rodrigues da Rocha
http://ezequiasrocha.blogspot.com/
use Mozilla Firefox:http://br.mozdev.org/firefox/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL to Oracle
@ 2007-03-09 18:06 Frank Bax <fbax@sympatico.ca>
0 siblings, 0 replies; 10+ messages in thread
From: Frank Bax @ 2007-03-09 18:06 UTC (permalink / raw)
To: pgsql-sql
At 12:54 PM 3/9/07, Ezequias Rodrigues da Rocha wrote:
>Is it a simple action to convert a database from PostgreSQL to Oracle ?
>
>I mean a simple database with
>
>33 tables
>8 functions
>31 sequencies
>2 triggers
>1 type
>3 views
>
>Has someone any idea ?
Depends on what's actually in the above objects; simple check each one to
see if Oracle has an equivalent. Another consideration - do the SQL
statements in your app adhere to standards?
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2007-03-16 18:20 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-09 17:54 PostgreSQL to Oracle Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-03-09 18:32 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-03-09 19:32 ` Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-03-15 20:20 ` Robert Treat <xzilla@users.sourceforge.net>
2007-03-15 20:47 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-03-16 17:18 ` Reece Hart <reece@harts.net>
2007-03-16 17:44 ` David Fetter <david@fetter.org>
2007-03-16 18:20 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-03-09 18:40 ` Martin Gainty <mgainty@hotmail.com>
2007-03-09 18:06 Re: PostgreSQL to Oracle Frank Bax <fbax@sympatico.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox