From ezequias.rocha@gmail.com Fri Mar 9 17:54:19 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 666509FB832 for ; Fri, 9 Mar 2007 13:54:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 90111-01 for ; Fri, 9 Mar 2007 13:54:15 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id E061C9FB782 for ; Fri, 9 Mar 2007 13:54:14 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1343070ugf for ; Fri, 09 Mar 2007 09:54:12 -0800 (PST) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=nuTWgpvjhpFm+GgEgxU40qLlX9ylQxaHENxskZD8DWU727C0ctMZCGzIC+zShyQ5w+6FLicHQu2sVQhhhZRXZXQ1ThiRNykWPJ/YRNaJi71HO+ldaVpQ1qvg6I8VVSB4HZ2RSZ30v8jesspHP4IuSUlXoU/bDQyINszBfQvELwA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=Tb+pSjsMSwQwOspPd3KVqny51MUZ9/nG8J9Wo+IwZSt1fwCPaJ0eyNd/4WCeeVBi0JHpZPs6BQaXNwEKCo0p7ikzFwaLLF681dDjKesIcxZw4NiDCGgNS4R/St6iEcxagm5CJkOHU9/ASAmg5T4QvTnJkk0h2o5xZxR5x/pZLLA= Received: by 10.67.26.7 with SMTP id d7mr10083961ugj.1173462852178; Fri, 09 Mar 2007 09:54:12 -0800 (PST) Received: by 10.67.94.13 with HTTP; Fri, 9 Mar 2007 09:54:12 -0800 (PST) Message-ID: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.com> Date: Fri, 9 Mar 2007 14:54:12 -0300 From: "Ezequias Rodrigues da Rocha" To: "pgsql general" , PostgreSQL Subject: PostgreSQL to Oracle MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_574_25057391.1173462852113" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/544 X-Sequence-Number: 110734 ------=_Part_574_25057391.1173462852113 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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/ ------=_Part_574_25057391.1173462852113 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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/ ------=_Part_574_25057391.1173462852113-- From fbax@sympatico.ca Fri Mar 9 18:06:21 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 417129FBC20 for ; Fri, 9 Mar 2007 14:06:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 88273-10 for ; Fri, 9 Mar 2007 14:06:16 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from tomts13-srv.bellnexxia.net (tomts13.bellnexxia.net [209.226.175.34]) by postgresql.org (Postfix) with ESMTP id B26579FB6A3 for ; Fri, 9 Mar 2007 14:06:16 -0400 (AST) Received: from acer.sympatico.ca ([65.95.132.190]) by tomts13-srv.bellnexxia.net (InterMail vM.5.01.06.13 201-253-122-130-113-20050324) with ESMTP id <20070309180615.UHFD1593.tomts13-srv.bellnexxia.net@acer.sympatico.ca> for ; Fri, 9 Mar 2007 13:06:15 -0500 Message-Id: <5.2.1.1.0.20070309130257.0390e6d0@pop6.sympatico.ca> X-Sender: b1wudu74@pop6.sympatico.ca X-Mailer: QUALCOMM Windows Eudora Version 5.2.1 Date: Fri, 09 Mar 2007 13:06:06 -0500 To: pgsql-sql@postgresql.org From: Frank Bax Subject: Re: PostgreSQL to Oracle In-Reply-To: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.co m> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.032 tagged_above=0 required=5 tests=AWL, BAYES_40, FORGED_RCVD_HELO, SPF_PASS X-Spam-Level: X-Archive-Number: 200703/126 X-Sequence-Number: 27764 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? From jonah.harris@gmail.com Fri Mar 9 18:32:39 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 804269FB8DF for ; Fri, 9 Mar 2007 14:32:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 90787-08 for ; Fri, 9 Mar 2007 14:32:21 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id D55FD9FB85B for ; Fri, 9 Mar 2007 14:32:22 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so1171652nfb for ; Fri, 09 Mar 2007 10:32:21 -0800 (PST) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=afo9mU0l6wsexlxoFWo8tKZq0w2BBxf1+h8cwZBiPVBRFG5h4yX9atr+AeA4qQqw056k5nxnYq+mVZ0Xtx30XlLXLnK3ODVKxDHXY5ixyTVAp1BwlWnAhxrYZQiyEblD+ys3cFGumRpO5Sfq/aRM3D5EX2Xzkp1aoBedT7/U+fs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=g/C4/5rDAwxG91gyUy+SAotJiXhlmKoOMOJxnJuJCsq49KdFPUQDF/FJRnT61pbOiYN95heNj+Krfg6mYp15+QYMokmj6tKifvlaaga/MkXKCkt34pZGp7YcjdMQDo4gKNQfiUfS2TxLOGg2rHO1ne7r+yQO6VhNR5CMLr8coI8= Received: by 10.114.58.1 with SMTP id g1mr658654waa.1173465140582; Fri, 09 Mar 2007 10:32:20 -0800 (PST) Received: by 10.114.183.2 with HTTP; Fri, 9 Mar 2007 10:32:20 -0800 (PST) Message-ID: <36e682920703091032u22cd9901w6212c9aba999ec8c@mail.gmail.com> Date: Fri, 9 Mar 2007 13:32:20 -0500 From: "Jonah H. Harris" To: "Ezequias Rodrigues da Rocha" Subject: Re: [SQL] PostgreSQL to Oracle Cc: "pgsql general" , PostgreSQL In-Reply-To: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/547 X-Sequence-Number: 110737 On 3/9/07, Ezequias Rodrigues da Rocha 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/ From mgainty@hotmail.com Fri Mar 9 18:40:20 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E5B139FB2DC for ; Fri, 9 Mar 2007 14:40:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 91948-02 for ; Fri, 9 Mar 2007 14:40:02 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from bay0-omc3-s39.bay0.hotmail.com (bay0-omc3-s39.bay0.hotmail.com [65.54.246.239]) by postgresql.org (Postfix) with ESMTP id 4B16B9FA34F for ; Fri, 9 Mar 2007 14:40:03 -0400 (AST) Received: from hotmail.com ([65.55.138.15]) by bay0-omc3-s39.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668); Fri, 9 Mar 2007 10:40:02 -0800 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Fri, 9 Mar 2007 10:40:02 -0800 Message-ID: Received: from 209.6.18.70 by BAY133-DAV5.phx.gbl with DAV; Fri, 09 Mar 2007 18:40:01 +0000 X-Originating-IP: [209.6.18.70] X-Originating-Email: [mgainty@hotmail.com] X-Sender: mgainty@hotmail.com Reply-To: "Martin Gainty" From: "Martin Gainty" To: "Ezequias Rodrigues da Rocha" , "pgsql general" References: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.com> Subject: Re: PostgreSQL to Oracle Date: Fri, 9 Mar 2007 13:40:00 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_007C_01C76250.6F1C2650" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.3028 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 X-OriginalArrivalTime: 09 Mar 2007 18:40:02.0209 (UTC) FILETIME=[59009110:01C7627A] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.241 tagged_above=0 required=5 tests=AWL, BAYES_00, HTML_40_50, HTML_MESSAGE, MSGID_FROM_MTA_HEADER, SPF_PASS X-Spam-Level: X-Archive-Number: 200703/548 X-Sequence-Number: 110738 This is a multi-part message in MIME format. ------=_NextPart_000_007C_01C76250.6F1C2650 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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/c21dl= ins.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 -- -------------------------------------------------------------------------= --=20 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. -------------------------------------------------------------------------= --=20 Le pr=E9sent message =E9lectronique (y compris les pi=E8ces qui y sont = annex=E9es, le cas =E9ch=E9ant) s'adresse au destinataire indiqu=E9 et = peut contenir des renseignements de caract=E8re priv=E9 ou confidentiel. = Si vous n'=EAtes 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 -----=20 From: Ezequias Rodrigues da Rocha=20 To: pgsql general ; PostgreSQL=20 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=20 33 tables 8 functions 31 sequencies 2 triggers 1 type 3 views Has someone any idea ?=20 --=20 Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/ ------=_NextPart_000_007C_01C76250.6F1C2650 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Ezequias-

first you will need to = pg_dump=20 everything.. schema..tables..functions then your Data into a text format = which=20 has no whitespace characters
There are 2 bulk loaders available from = Oracle
1)Brand new DataPump
2)Tried = and true=20 sqlldr (which I recommend)
I would = highly=20 recommend reading and understanding the Extract / Transform /Load = document=20 located at
http://www.oracle.com/technology/obe/obe10gdb/bidw/etl2/etl2.htm
Also triple check the extents you are = allocating for the=20 tablespace for tables to be inserted into that tablespace = will=20 guaranteed to be sufficient size
(If you dont know the size of a = block=20 ask..as this isnt something you can fix later..unless you want to spend = a 3day=20 weekend doing a rollback..)
Become familiar with LOAD DATA INFILE = command=20 links available from either Stanford (or = Oracle)
htt= p://infolab.stanford.edu/~ullman/fcdb/oracle/or-load.html
Be mindful that Oracle DB is used by = ALL of the=20 major players
so inserting nulls which could violate = any=20 constraint (constraints being unique or foreign key) are = forbidden
In fact inserting nulls into any column = is=20 discouraged as it may violate the relationship of dependent table = records to=20 primary table
If you have = time and or=20 diskspace constraint you may want to look at Direct Path = Loading which=20 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=20 logger is turned off and if there are any burps along the way you = wont=20 know which record it burped on

Feel free to ping me if you have any = questions/

Saludos=20 Cordiales,
Martin --
--------------------------------------------------------------------= -------=20
This e-mail message (including attachments, if any) is intended for = the use=20 of the individual or entity to which it is addressed and may contain = information=20 that is privileged, proprietary , confidential and exempt from = disclosure. If=20 you are not the intended recipient, you are notified that any = dissemination,=20 distribution or copying of this communication is strictly=20 prohibited.
----------------------------------------------------------= -----------------=20
Le pr=E9sent message =E9lectronique (y compris les pi=E8ces qui y = sont annex=E9es,=20 le cas =E9ch=E9ant) s'adresse au destinataire indiqu=E9 et peut contenir = des=20 renseignements de caract=E8re priv=E9 ou confidentiel. Si vous n'=EAtes = pas le=20 destinataire de ce document, nous vous signalons qu'il est strictement = interdit=20 de le diffuser, de le distribuer ou de le reproduire.
----- Original Message -----
From:=20 Ezequias Rodrigues da = Rocha
To: pgsql general ; PostgreSQL
Sent: Friday, March 09, 2007 = 12:54=20 PM
Subject: [GENERAL] PostgreSQL = to=20 Oracle

Hi list,

Is it a simple action to convert a = database=20 from PostgreSQL to Oracle ?

I mean a simple database with =

33=20 tables
8 functions
31 sequencies
2 triggers
1 type
3=20 views

Has someone any idea ?

-- =
Ezequias=20 Rodrigues da Rocha
http://ezequiasrocha.blogspot= com/
use=20 Mozilla Firefox:http://br.mozdev.org/firefox/=20 ------=_NextPart_000_007C_01C76250.6F1C2650-- From ezequias.rocha@gmail.com Fri Mar 9 19:32:21 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id B4B199FB43B for ; Fri, 9 Mar 2007 15:32:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 21871-02 for ; Fri, 9 Mar 2007 15:32:15 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id C9DA99FB5FA for ; Fri, 9 Mar 2007 15:32:16 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1372573ugf for ; Fri, 09 Mar 2007 11:32:12 -0800 (PST) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=HQnkAumchEgKCxNrigJpXXm8mXHVmtxsjENPi7T/kFRjepOHvKwHOrsvxoCPkyHHsajU0ljn3qanTVfUb6AAmxcIaXwybZ827Lqj4ezhsQ2sAKsekU4/xQ0cfzNQitdRN9ihiD1JSDsDwrvjeA3du8J98z/BFq3TtFEIhVhAp6k= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=VrWMPUPM21HU04zSsMSeLz1X5uHjUZuEoyD39snzzT/ST9kYZDbCf8W3WaKdIP6h75/kuCZmbURMx0p8niQBcb/r3+RRGIBa0DUUSaUnlVeii/SWzgeyNQTrX+MGW/9dFRWD7EfzxkUv6Cz66TjFVmw4oGuuoEQsr8EBsj7qefw= Received: by 10.67.121.15 with SMTP id y15mr10249912ugm.1173468732466; Fri, 09 Mar 2007 11:32:12 -0800 (PST) Received: by 10.67.94.13 with HTTP; Fri, 9 Mar 2007 11:32:12 -0800 (PST) Message-ID: <55c095e90703091132y6f63c912ua816946d2a0b198a@mail.gmail.com> Date: Fri, 9 Mar 2007 16:32:12 -0300 From: "Ezequias Rodrigues da Rocha" To: "Jonah H. Harris" Subject: Re: [SQL] PostgreSQL to Oracle Cc: "pgsql general" , PostgreSQL In-Reply-To: <36e682920703091032u22cd9901w6212c9aba999ec8c@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1679_872.1173468732402" References: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.com> <36e682920703091032u22cd9901w6212c9aba999ec8c@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/556 X-Sequence-Number: 110746 ------=_Part_1679_872.1173468732402 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 : > > On 3/9/07, Ezequias Rodrigues da Rocha 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/ ------=_Part_1679_872.1173468732402 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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/ ------=_Part_1679_872.1173468732402-- From xzilla@users.sourceforge.net Thu Mar 15 20:21:06 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 4FE899FBBE4 for ; Thu, 15 Mar 2007 17:21:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 43534-02 for ; Thu, 15 Mar 2007 17:20:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from vms046pub.verizon.net (vms046pub.verizon.net [206.46.252.46]) by postgresql.org (Postfix) with ESMTP id 2F6CF9FB859 for ; Thu, 15 Mar 2007 17:20:57 -0300 (ADT) Received: from new-host.home ([71.166.59.121]) by vms046.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0JEY00BTFOIFY6H1@vms046.mailsrvcs.net> for pgsql-general@postgresql.org; Thu, 15 Mar 2007 15:20:42 -0500 (CDT) Date: Thu, 15 Mar 2007 16:20:34 -0400 From: Robert Treat Subject: Re: [SQL] PostgreSQL to Oracle In-reply-to: <36e682920703091032u22cd9901w6212c9aba999ec8c@mail.gmail.com> To: pgsql-general@postgresql.org Cc: "Jonah H. Harris" , "Ezequias Rodrigues da Rocha" Message-id: <200703151620.34309.xzilla@users.sourceforge.net> MIME-version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: 7bit Content-disposition: inline References: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.com> <36e682920703091032u22cd9901w6212c9aba999ec8c@mail.gmail.com> User-Agent: KMail/1.9.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/933 X-Sequence-Number: 111122 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 From jonah.harris@gmail.com Thu Mar 15 20:47:53 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2F8269FB8CB for ; Thu, 15 Mar 2007 17:47:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 80505-03 for ; Thu, 15 Mar 2007 17:47:45 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.233]) by postgresql.org (Postfix) with ESMTP id 508719FB7B9 for ; Thu, 15 Mar 2007 17:47:48 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 76so260233wra for ; Thu, 15 Mar 2007 13:47:47 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=VjfnYu5AfGJyJRW6Gmag0vFovrlsMDhROzUQCuFCbwKuA7YkA0lwDZP834CIUaG6FVkG56FiE87QwVPSX8f62uVAGGW+8eLbIXJgEaQcXTPBYOtGOHaRzvT5Da61Ino0g6fycRMp9eIMz0c363V+m+Tlf+BUpg2dz23F6OC4TU4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=ioB0WSMxf47DYlVUZYaNP2sk8iedmREIXIkA+CX03VC+u06NsHJC09yYLQGVXiEAoClbrHuNoyer3q5kttNdZgmS+FSZfkyZbsr9jd+M3J8lHSQ4rxs3yE8qBJD+MxeP17+FOpoQ1LRuac39YgVPJvH+YsgryK+14UH7uGNtbUw= Received: by 10.114.13.1 with SMTP id 1mr422083wam.1173991666731; Thu, 15 Mar 2007 13:47:46 -0700 (PDT) Received: by 10.114.181.14 with HTTP; Thu, 15 Mar 2007 13:47:46 -0700 (PDT) Message-ID: <36e682920703151347o10fb0dcesa68d7f1e6542232@mail.gmail.com> Date: Thu, 15 Mar 2007 16:47:46 -0400 From: "Jonah H. Harris" To: "Robert Treat" Subject: Re: [SQL] PostgreSQL to Oracle Cc: pgsql-general@postgresql.org, "Ezequias Rodrigues da Rocha" In-Reply-To: <200703151620.34309.xzilla@users.sourceforge.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.com> <36e682920703091032u22cd9901w6212c9aba999ec8c@mail.gmail.com> <200703151620.34309.xzilla@users.sourceforge.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/939 X-Sequence-Number: 111128 On 3/15/07, Robert Treat 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/ From reece@harts.net Fri Mar 16 17:19:13 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 746B99FBF3B for ; Fri, 16 Mar 2007 14:19:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 12698-05 for ; Fri, 16 Mar 2007 14:19:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from outbound-mail-17.bluehost.com (outbound-mail-17.bluehost.com [69.89.20.232]) by postgresql.org (Postfix) with SMTP id 178119FBF31 for ; Fri, 16 Mar 2007 14:19:05 -0300 (ADT) Received: (qmail 22933 invoked by uid 0); 16 Mar 2007 17:19:01 -0000 Received: from unknown (HELO host28.hostmonster.com) (70.98.54.28) by mailproxy1.bluehost.com with SMTP; 16 Mar 2007 17:19:01 -0000 Received: from fw-open-virt.gene.com ([192.12.78.250] helo=[128.137.187.112]) by host28.hostmonster.com with esmtpa (Exim 4.63) (envelope-from ) id 1HSG4y-0004bl-UA; Fri, 16 Mar 2007 11:19:01 -0600 Subject: Re: [SQL] PostgreSQL to Oracle From: Reece Hart To: Robert Treat Cc: pgsql-general@postgresql.org, "Jonah H. Harris" , Ezequias Rodrigues da Rocha In-Reply-To: <200703151620.34309.xzilla@users.sourceforge.net> References: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.com> <36e682920703091032u22cd9901w6212c9aba999ec8c@mail.gmail.com> <200703151620.34309.xzilla@users.sourceforge.net> Content-Type: text/plain Date: Fri, 16 Mar 2007 10:18:56 -0700 Message-Id: <1174065536.5244.22.camel@snafu.site> Mime-Version: 1.0 X-Mailer: Evolution 2.8.2 Content-Transfer-Encoding: 7bit X-Identified-User: {900:host28.hostmonster.com:hartsnet:harts.net} {sentby:smtp auth 192.12.78.250 authed with reece@harts.net} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/1006 X-Sequence-Number: 111195 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. From david@fetter.org Fri Mar 16 17:44:10 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C4F899FBDBF for ; Fri, 16 Mar 2007 14:44:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 66988-04 for ; Fri, 16 Mar 2007 14:44:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from fetter.org (start.fetter.org [66.92.188.65]) by postgresql.org (Postfix) with ESMTP id A7AEB9FB656 for ; Fri, 16 Mar 2007 14:44:05 -0300 (ADT) Received: by fetter.org (Postfix, from userid 500) id 59A14F3CC6D; Fri, 16 Mar 2007 10:44:04 -0700 (PDT) Date: Fri, 16 Mar 2007 10:44:04 -0700 From: David Fetter To: Reece Hart Cc: Robert Treat , pgsql-general@postgresql.org, "Jonah H. Harris" , Ezequias Rodrigues da Rocha Subject: Re: [SQL] PostgreSQL to Oracle Message-ID: <20070316174404.GC8169@fetter.org> References: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.com> <36e682920703091032u22cd9901w6212c9aba999ec8c@mail.gmail.com> <200703151620.34309.xzilla@users.sourceforge.net> <1174065536.5244.22.camel@snafu.site> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1174065536.5244.22.camel@snafu.site> User-Agent: Mutt/1.4.2.2i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/1007 X-Sequence-Number: 111196 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 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 From jonah.harris@gmail.com Fri Mar 16 18:20:46 2007 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 3A5A79FBEE0 for ; Fri, 16 Mar 2007 15:20:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 34123-03 for ; Fri, 16 Mar 2007 15:20:38 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ik-out-1112.google.com (ik-out-1112.google.com [66.249.90.180]) by postgresql.org (Postfix) with ESMTP id 101B19FB78E for ; Fri, 16 Mar 2007 15:20:37 -0300 (ADT) Received: by ik-out-1112.google.com with SMTP id c21so500843ika for ; Fri, 16 Mar 2007 11:20:36 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=n/ODVoxIK0mErnX/O2PND0h9aBd9U129QGBhw4E4fPaf9TLCix1hrdKReejktaHlTuBwLKXU4hHhZL4ZBwVVBmeFqzoy2XIMyoZpZQvv9ypAqs8HpVf2gJfsWOTNhWNQU2x8PTjYyEkpXE6sKknjoddMM7mwM5VFyC8B1WJ2OBA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=n6Fm14N7X6GZetjbn4QRsv87h+ioDQS+Exy3FlZEA7Z6CbFiP17SrCRJV5Q19KIK8KUGcjihTHRmBRo4iH0akw032DNmGT56aHDxkhKQ4SooyKEVbffqaLJoOef33Gyw4J21Ay0GyoeiYZSc/GIQYZZkmeyjh0fD4DAfwsG+bes= Received: by 10.114.152.17 with SMTP id z17mr841128wad.1174069236023; Fri, 16 Mar 2007 11:20:36 -0700 (PDT) Received: by 10.114.181.14 with HTTP; Fri, 16 Mar 2007 11:20:35 -0700 (PDT) Message-ID: <36e682920703161120n25cba238sab8080cb8f703fc8@mail.gmail.com> Date: Fri, 16 Mar 2007 14:20:35 -0400 From: "Jonah H. Harris" To: "David Fetter" Subject: Re: [SQL] PostgreSQL to Oracle Cc: "Reece Hart" , "Robert Treat" , pgsql-general@postgresql.org, "Ezequias Rodrigues da Rocha" In-Reply-To: <20070316174404.GC8169@fetter.org> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.com> <36e682920703091032u22cd9901w6212c9aba999ec8c@mail.gmail.com> <200703151620.34309.xzilla@users.sourceforge.net> <1174065536.5244.22.camel@snafu.site> <20070316174404.GC8169@fetter.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/1008 X-Sequence-Number: 111197 On 3/16/07, David Fetter 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/