Re: [GENERAL] Oracle to PostgreSQL

[Felix Kunde]

On Thu, 2017-11-09 at 17:01 +0530, Brahmam Eswar wrote:
> You can create a composite type in PostgreSQL:
> CREATE TYPE complex AS (r integer, i integer);
> You would use an array in this case:
> DECLARE
> carr complex[];

I've once faced the same thing, and did as Laurenz suggested.
You will like Postgres' array support.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL

[Laurenz Albe]

On Thu, 2017-11-09 at 17:01 +0530, Brahmam Eswar wrote:
> Here is the snippet of it.

>TYPE INV_LINES_RT IS RECORD(
>  VENDOR_NUM A.Datastore.VENDOR_NUM%TYPE,
>  VENDOR_SITE_CODE A.Datastore.VENDOR_SITE_CODE%TYPE,
>  INVOICE_NUM A.Datastore.INVOICE_NUM%TYPE,
>  TXN_CNT NUMBER
>);

You can create a composite type in PostgreSQL:

   CREATE TYPE complex AS (r integer, i integer);

>TYPE INV_LINES_T IS TABLE OF INV_LINES_RT;

You would use an array in this case:

   DECLARE
  carr complex[];

Yours,
Laurenz Albe


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL

[Brahmam Eswar]

Here is the snippet of it.

*Oracle :*

*Declaration part in Store Procedure*

 CREATE OR REPLACE PROCEDURE "A"."Datastore"
 (

 , In_Param1 IN VARCHAR2
 , In_Param2 IN VARCHAR2
 , In_Param3 IN VARCHAR2
 , Out_Param1 OUT VARCHAR2
 , ERROR_MSG OUT VARCHAR2
 ) AS
   TEMP_ERR_MSG VARCHAR2(4000);
   IS_FIRST CHAR(1);
   TYPE INV_LINES_RT *IS RECORD*(
 VENDOR_NUM A.Datastore.VENDOR_NUM%TYPE,
 VENDOR_SITE_CODE A.Datastore.VENDOR_SITE_CODE%TYPE,
 INVOICE_NUM A.Datastore.INVOICE_NUM%TYPE,
 TXN_CNT NUMBER
   );
   TYPE INV_LINES_T *IS TABLE OF* INV_LINES_RT;
   L_INV_LINES INV_LINES_T;


*Looping Part :*

 IS_FIRST:='Y';
 WITH Distinct_Invoices As ( select DISTINCT
VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM,DESCRIPTION,CURR_CODE,INVOICE_AMOUNT,IMAGE_NUMBER,
VENDOR_NAME, PAYMENT_METHOD, GL_DATE, TERMS_NAME, RETURN_ID, PAYGROUP,
INVOICE_TYPE, INVOICE_DATE from A.Datastore where CASE_ID = In_Param1 )
 Select VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM, count(*) as TXN_CNT * BULK
COLLECT INTO* L_INV_LINES from Distinct_Invoices group by
VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM having count(*) > 1;
   IF  L_INV_LINES.COUNT>0 THEN

 ERROR_COUNT:=0;
   FOR indx in L_INV_LINES.first .. L_INV_LINES.last LOOP
 IF IS_FIRST!='Y' THEN
  TEMP_ERR_MSG  :=  TEMP_ERR_MSG || ', ';
 END IF;
 IS_FIRST:='N';
 TEMP_ERR_MSG  :=  TEMP_ERR_MSG ||  ||
L_INV_LINES(indx).INVOICE_NUM || ;


How do i convert this into PostgreSQL.

On Thu, Nov 9, 2017 at 4:48 PM, Laurenz Albe 
wrote:

> Brahmam Eswar wrote:
> > How to migrate the Oracle collection types ( IsTableOF, IsRecord) to
> postgres.
>
> Are you talking about table definitions or PL/SQL code?
>
> Yours,
> Laurenz Albe
>



-- 
Thanks & Regards,
Brahmeswara Rao J.

Re: [GENERAL] Oracle to PostgreSQL

[Laurenz Albe]

Brahmam Eswar wrote:
> How to migrate the Oracle collection types ( IsTableOF, IsRecord) to postgres.

Are you talking about table definitions or PL/SQL code?

Yours,
Laurenz Albe


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Oracle to PostgreSQL

[Brahmam Eswar]

Hi ,

How to migrate the Oracle collection types ( IsTableOF, IsRecord) to
postgres.

-- 
Thanks & Regards,
Brahmeswara Rao J.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 7:17 PM, Adrian Klaver 
wrote:

> On 05/29/2017 06:40 AM, PAWAN SHARMA wrote:
>
>>
>>
> ya, but how can fix it.??
>>
>> because memory is not an issue on both the server.
>>
>>
>> Hi Chris,
>>
>> I am able to get the output script in output.sql but now data migration
>> is the big problem.
>>
>
> http://ora2pg.darold.net/documentation.html#postgresql_import
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


Thanks all,

now it's working After DATA_LIMIT15000 in ora2pg.conf.

test migration is successful.


C:\ora2pg>ora2pg -c ora2pg.conf
[>] 2/2 tables (100.0%) end of scanning.
[>] 0/2 tables (0.0%) end of scanning.
[>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=;sid=;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;

CREATE TABLE test (
id bigint,
name varchar(30)
) ;
[>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[>] 1/2 total rows (50.0%) - (4 sec., avg: 0
recs/sec).
[>] 1/1 rows (100.0%) Table TEST (0 recs/sec)
[>] 2/2 total rows (100.0%) - (261 sec., avg: 0
recs/sec).
[>] 2/2 rows (100.0%) on total estimated data (262
sec., avg: 0 recs/sec)
C:\ora2pg>

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Adrian Klaver]

On 05/29/2017 06:40 AM, PAWAN SHARMA wrote:





ya, but how can fix it.??

because memory is not an issue on both the server.


Hi Chris,

I am able to get the output script in output.sql but now data migration 
is the big problem.


http://ora2pg.darold.net/documentation.html#postgresql_import

--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Neil Anderson]

>>>
>>>
>> ya, but how can fix it.??
>>
>> because memory is not an issue on both the server.
>>
>

I've never used ora2pg but there is some documentation saying that
often an out of memory issue can be fixed by adjusting your DATA_LIMIT
setting. https://github.com/darold/ora2pg/blob/master/README#L307

> Hi Chris,
>
> I am able to get the output script in output.sql but now data migration is
> the big problem.

-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 6:54 PM, PAWAN SHARMA 
wrote:

>
>
> On Mon, May 29, 2017 at 6:51 PM, Chris Mair  wrote:
>
>> [>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
>>> [>] 1/2 total rows (50.0%) - (4 sec., avg: 0
>>> recs/sec).
>>> Out of memory!] 1/2 rows (50.0%) on total estimated data (4
>>> sec., avg: 0 recs/sec)
>>> Issuing rollback() due to DESTROY without explicit disconnect() of
>>> DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
>>> RT=1521)(PROTOCOL=tcp)(HOST=))(CONNECT_DATA=(SID=)))
>>> at C:/Strawberry/perl/vendor/
>>> lib/DBD/Oracle.pm line 348.
>>>
>>>
>>> So, It will show data enter in mytab 100% but in test it is 50%. but
>>> when I checked on PostgreSQL server their data only in one table.
>>>
>>
>> Did you notice the "Out of memory!" you got there?
>>
>> I guess that's the problem now...
>>
>> Bye,
>> Chris.
>>
>>
>>
>>
>>
>> ya, but how can fix it.??
>
> because memory is not an issue on both the server.
>
>
Hi Chris,

I am able to get the output script in output.sql but now data migration is
the big problem.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 6:51 PM, Chris Mair  wrote:

> [>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
>> [>] 1/2 total rows (50.0%) - (4 sec., avg: 0
>> recs/sec).
>> Out of memory!] 1/2 rows (50.0%) on total estimated data (4
>> sec., avg: 0 recs/sec)
>> Issuing rollback() due to DESTROY without explicit disconnect() of
>> DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
>> RT=1521)(PROTOCOL=tcp)(HOST=))(CONNECT_DATA=(SID=)))
>> at C:/Strawberry/perl/vendor/
>> lib/DBD/Oracle.pm line 348.
>>
>>
>> So, It will show data enter in mytab 100% but in test it is 50%. but when
>> I checked on PostgreSQL server their data only in one table.
>>
>
> Did you notice the "Out of memory!" you got there?
>
> I guess that's the problem now...
>
> Bye,
> Chris.
>
>
>
>
>
> ya, but how can fix it.??

because memory is not an issue on both the server.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Chris Mair]

[>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[>] 1/2 total rows (50.0%) - (4 sec., avg: 0 recs/sec).
Out of memory!] 1/2 rows (50.0%) on total estimated data (4 sec., 
avg: 0 recs/sec)
Issuing rollback() due to DESTROY without explicit disconnect() of 
DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
RT=1521)(PROTOCOL=tcp)(HOST=))(CONNECT_DATA=(SID=))) at 
C:/Strawberry/perl/vendor/
lib/DBD/Oracle.pm line 348.


So, It will show data enter in mytab 100% but in test it is 50%. but when I 
checked on PostgreSQL server their data only in one table.


Did you notice the "Out of memory!" you got there?

I guess that's the problem now...

Bye,
Chris.








--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 6:28 PM, Chris Mair  wrote:

> C:\ora2pg>ora2pg -c ora2pg.conf
>> [>] 2/2 tables (100.0%) end of scanning.
>> [>] 0/2 tables (0.0%) end of scanning.
>> [>] 2/2 tables (100.0%) end of table export.
>>
>
> Looks good so far.
> This means you could connect to Oracle DB now.
>
>
> DBD::Pg::st execute failed: ERROR:  relation "mytab" does not exist
>>
>
> This is coming from the Postgres side.
>
> In ora2pg.conf go to the section
>
> OUTPUT SECTION (Control output to file or PostgreSQL database)
>
> I suggest you comment out (prefix with #) the part
>
> #PG_DSN dbi:Pg:dbname=test_db;host=localhost;port=5432
> #PG_USERtest
> #PG_PWD test
>
> and just have ora2pg write its ouput to a file by setting OUTPUT like this:
>
> OUTPUT  output.sql
>
> This way you have your oputput for Postgres in a file that you can check
> out
> and try importing step by step. I guess you are running this on some test
> data, so the file will be small enough to open it with an editor.
> You cap paste piece by piece into a Postgres prompt (psql or pgadmin or
> whatever
> you're using).
>
> You can then see at what point you get an error (and hopefully understand
> what's happening).
>
> Bye,
> Chris.
>
>
>
Hi Chris,

Thanks for suggestion.!!!

here in ora2pg.conf, I have used below type in ora2pg.conf and create the
table manually on PostgreSQL server.


*TYPETABLE*


*output after this*
-
C:\ora2pg>ora2pg -c ora2pg.conf
Ora2Pg version: 18.1
Trying to connect to database:
dbi:Oracle:host=;sid=;port=1521
Isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Retrieving table information...
[1] Scanning table MYTAB (1 rows)...
[2] Scanning table TEST (1 rows)...
Dumping table TEST...
Dumping table MYTAB...
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=;sid=;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE test (
id bigint,
name varchar(30)
) ;

CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;


but, when i am trying to insert data using

TYPE   TABLE, INSERT


C:\ora2pg>ora2pg -c ora2pg.conf
[>] 2/2 tables (100.0%) end of scanning.
[>] 0/2 tables (0.0%) end of scanning.
[>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=;sid=;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;

CREATE TABLE test (
id bigint,
name varchar(30)
) ;
[>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[>] 1/2 total rows (50.0%) - (4 sec., avg: 0
recs/sec).
Out of memory!] 1/2 rows (50.0%) on total estimated data (4
sec., avg: 0 recs/sec)
Issuing rollback() due to DESTROY without explicit disconnect() of
DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
RT=1521)(PROTOCOL=tcp)(HOST=))(CONNECT_DATA=(SID=))) at
C:/Strawberry/perl/vendor/
lib/DBD/Oracle.pm line 348.


So, It will show data enter in mytab 100% but in test it is 50%. but when I
checked on PostgreSQL server their data only in one table.

test=# select * from mytab ;
 id | name | dt
+--+-
  1 | aa   | 2017-05-29 06:05:46
(1 row)

test=# select * from test;
 id | name
+--
(0 rows)




So, I am stuck here..!!! Please suggest.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Chris Mair]

C:\ora2pg>ora2pg -c ora2pg.conf
[>] 2/2 tables (100.0%) end of scanning.
[>] 0/2 tables (0.0%) end of scanning.
[>] 2/2 tables (100.0%) end of table export.


Looks good so far.
This means you could connect to Oracle DB now.



DBD::Pg::st execute failed: ERROR:  relation "mytab" does not exist


This is coming from the Postgres side.

In ora2pg.conf go to the section

OUTPUT SECTION (Control output to file or PostgreSQL database)

I suggest you comment out (prefix with #) the part

#PG_DSN dbi:Pg:dbname=test_db;host=localhost;port=5432
#PG_USERtest
#PG_PWD test

and just have ora2pg write its ouput to a file by setting OUTPUT like this:

OUTPUT  output.sql

This way you have your oputput for Postgres in a file that you can check out
and try importing step by step. I guess you are running this on some test
data, so the file will be small enough to open it with an editor.
You cap paste piece by piece into a Postgres prompt (psql or pgadmin or whatever
you're using).

You can then see at what point you get an error (and hopefully understand
what's happening).

Bye,
Chris.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 2:49 PM, Chris Mair  wrote:

> I am facing below issue while running below command.
>>
>> *C:\ora2pg>ora2pg -c ora2pg.conf*
>> DBI connect('host=;sid=;port=',',...)
>> failed: ORA-01017: invalid username/p
>> assword; logon denied (DBD ERROR: OCISessionBegin) at
>> C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1376.
>> FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD
>> ERROR: OCISessionBegin)
>> Aborting export...
>>
>> even I am able to login in Oracle with same  and 
>>
>
> If you can login from the same host using sqlplus like this:
>
> sqlplus myuser/mypass@myhost:1521/mysid
>
> then you likely can have ora2pg connect too. Just make sure that in
> ora2pg.conf you put the lines:
>
> ORACLE_DSN  dbi:Oracle:host=myhost;sid=mysid
> ORACLE_USER myuser
> ORACLE_PWD  mypass
>
> Bye,
> Chris.
>
>
>
Hi Chris,

C:\ora2pg>ora2pg -c ora2pg.conf
[>] 2/2 tables (100.0%) end of scanning.
[>] 0/2 tables (0.0%) end of scanning.
[>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=;sid=;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE test (
id bigint,
name varchar(30)
) ;

CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;
DBD::Pg::st execute failed: ERROR:  relation "mytab" does not exist
LINE 1: INSERT INTO mytab (id,name,dt) VALUES ($1,$2,$3)
^ at C:/Strawberry/perl/site/lib/Ora2Pg.pm line 11574.
FATAL: ERROR:  relation "mytab" does not exist
LINE 1: INSERT INTO mytab (id,name,dt) VALUES ($1,$2,$3)
^
Aborting export...

C:\ora2pg>

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Chris Mair]

I am facing below issue while running below command.

*C:\ora2pg>ora2pg -c ora2pg.conf*
DBI connect('host=;sid=;port=',',...) failed: 
ORA-01017: invalid username/p
assword; logon denied (DBD ERROR: OCISessionBegin) at 
C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1376.
FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD ERROR: 
OCISessionBegin)
Aborting export...

even I am able to login in Oracle with same  and 


If you can login from the same host using sqlplus like this:

sqlplus myuser/mypass@myhost:1521/mysid

then you likely can have ora2pg connect too. Just make sure that in ora2pg.conf 
you put the lines:

ORACLE_DSN  dbi:Oracle:host=myhost;sid=mysid
ORACLE_USER myuser
ORACLE_PWD  mypass

Bye,
Chris.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

Hi All,

I am going to migrate Oracle database into PostgreSQL using ora2pg tools.

I have installed Strawberry Perl.

C:\ora2pg>perl -v
This is perl 5, version 24, subversion 1 (v5.24.1) built for
MSWin32-x64-multi-thread
Copyright 1987-2017, Larry Wall


I am facing below issue while running below command.

*C:\ora2pg>ora2pg -c ora2pg.conf*
DBI connect('host=;sid=;port=',',...)
failed: ORA-01017: invalid username/p
assword; logon denied (DBD ERROR: OCISessionBegin) at
C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1376.
FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD
ERROR: OCISessionBegin)
Aborting export...

even I am able to login in Oracle with same  and 

*entry in sqlnet.ora*

SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Please suggest.!!

-Pawan

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Julien Rouhaud]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Le 08/07/2015 22:25, CS DBA a écrit :
> 
> 
> On 07/08/2015 02:20 PM, John R Pierce wrote:
>> On 7/8/2015 1:16 PM, dinesh kumar wrote:
>>> We recently done the similar migration for one of our customer.
>>> We used all opensource tools to achieve this migration
>>> process.
>>> 
>>> We used Pentaho Data Integration tool for doing Online DB
>>> migration, which took minimal downtime with CDC{Change Data
>>> Capture} approach. Also, we used Ora2Pg tool to migrate the DB
>>> objects with some manual syntax modifications.
>>> 
>> 
>> thats the easy part.
>> 
>> now what about the massive code base of pl/sql and triggers he 
>> mentioned ?
>> 
>> 
>> 
> Have you considered using ora2pg? http://ora2pg.darold.net/
> 
> We've done several client migrations with it, quite successfully
> 
> 
> 

ora2pg is definitely the tool you need.  It can give you a total
migration estimated time (in man-day unit), with a call like

ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html --estimate_cost

You can check a sample report here: http://ora2pg.darold.net/report.html

- -- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.17 (GNU/Linux)

iQEcBAEBAgAGBQJVnimjAAoJELGaJ8vfEpOqTKAH/0+uGEXUmGm6tfagpJqU7kB+
2c+jooW/MKokDcgifvmUTy+fKb8iDoF8CUffActFyX5YyrCFfb4Bjw9P6wuJfF6S
WXhzWXQ//AFiApqNPknfHWnYeqe4jJlLq2fHN7qCQvItEWuKFiHpWcEi1zVBPnMm
e6NLxePm0WzjpigbwhT2X0Ziena8CxxdencPQvO81clsR8Fgtq4B//6KQ3GEsIL2
aUtj4k+wBCmRywiMgiSYiQzLUXUp2HWOp6qLLYpwifr4BgY2X+CQNSHlAK2KXecf
fQ+rm3tyo5QEtMxHQPO/NUsl+zSIllZjPYG1Wa81RwsQpWKhNNB/+reDTSgC5ws=
=9pb/
-END PGP SIGNATURE-


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[William Dunn]

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake 
 wrote:

>
> On 07/08/2015 12:47 PM, John McKown wrote:
>
>
>> ​Why are they converting?
>>
>> Would EnterpriseDB (a commercial version of PostgreSQL which has
>> extensions to make it a "drop in" replacement for Oracle) be a
>> possibility?
>> http://www.enterprisedb.com/solutions/oracle-compatibility-technology
>>
>
> Because EDB is expensive. Why go from one closed source solution to
> another when you can go to the best Open Source database and forgo all of
> that?
>
> Yes, EDB has some nice tools (no denying that) but porting from Oracle to
> PostgreSQL proper is not difficult in the least (although time consuming).


I would suggest refrain from dismissing EnterpriseDB's PostgreSQL Advanced
Server like that. It is not free like the community version of Postgres but
the cost pays for developer time spent adding the additional features which
make it capable of being a drop-in replacement of Oracle. For an
organization migrating off of Oracle paying for that would make a lot of
sense because of the developer time (and cost) saved by porting to that
rather than the additional effort of migration to community Postgres. It
also has the additional benefit of providing all the programming features
that their app developers have become used to when working with Oracle.

The EnterpriseDB developers are some of the most active contributors to the
community version of Postgres and the advancements made in the community
version are included in EnterpriseDB Advanced Server as well.

*Will J. Dunn*
*willjdunn.com *

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake 
wrote:

>
> On 07/08/2015 12:47 PM, John McKown wrote:
>
>
>> ​Why are they converting?
>>
>> Would EnterpriseDB (a commercial version of PostgreSQL which has
>> extensions to make it a "drop in" replacement for Oracle) be a
>> possibility?
>> http://www.enterprisedb.com/solutions/oracle-compatibility-technology
>>
>
> Because EDB is expensive. Why go from one closed source solution to
> another when you can go to the best Open Source database and forgo all of
> that?
>
> Yes, EDB has some nice tools (no denying that) but porting from Oracle to
> PostgreSQL proper is not difficult in the least (although time consuming).
>
> Sincerely,
>
> JD
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Announcing "I'm offended" is basically telling the world you can't
> control your own emotions, so everyone else should do it for you.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Melvin Davidson]

I would start by looking at how many databases, schemas, tables and views
are involved. Then look at how many individual Oracle functions need to be
converted to plpgsql. You also need to investigate if there are any custom
data types. I do not have the formula, but I am sure there is a general
time factor involved in converting x databases, y schemas and z tables. You
did not mention what hardware is involved, but I am also sure there is a
time factor involved in copying / converting data from Oracle to
PostgreSQL, and you need to consider how much data you need to move and the
order, as there are probably foreign keys involved also.

You might want to do a small test to see how long it takes to dump 10k rows
of data from 1 tOracle table and load to PostgreSQL. That will at least
give you a general idea of how long it will take to move all data.

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake 
wrote:

>
> On 07/08/2015 12:47 PM, John McKown wrote:
>
>
>> ​Why are they converting?
>>
>> Would EnterpriseDB (a commercial version of PostgreSQL which has
>> extensions to make it a "drop in" replacement for Oracle) be a
>> possibility?
>> http://www.enterprisedb.com/solutions/oracle-compatibility-technology
>>
>
> Because EDB is expensive. Why go from one closed source solution to
> another when you can go to the best Open Source database and forgo all of
> that?
>
> Yes, EDB has some nice tools (no denying that) but porting from Oracle to
> PostgreSQL proper is not difficult in the least (although time consuming).
>
> Sincerely,
>
> JD
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Announcing "I'm offended" is basically telling the world you can't
> control your own emotions, so everyone else should do it for you.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Joshua D. Drake]

On 07/08/2015 12:47 PM, John McKown wrote:



​Why are they converting?

Would EnterpriseDB (a commercial version of PostgreSQL which has
extensions to make it a "drop in" replacement for Oracle) be a possibility?
http://www.enterprisedb.com/solutions/oracle-compatibility-technology


Because EDB is expensive. Why go from one closed source solution to 
another when you can go to the best Open Source database and forgo all 
of that?


Yes, EDB has some nice tools (no denying that) but porting from Oracle 
to PostgreSQL proper is not difficult in the least (although time 
consuming).


Sincerely,

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[dinesh kumar]

On Wed, Jul 8, 2015 at 1:20 PM, John R Pierce  wrote:

> On 7/8/2015 1:16 PM, dinesh kumar wrote:
>
>> We recently done the similar migration for one of our customer. We used
>> all opensource tools to achieve this migration process.
>>
>> We used Pentaho Data Integration tool for doing Online DB migration,
>> which took minimal downtime with CDC{Change Data Capture} approach. Also,
>> we used Ora2Pg tool to migrate the DB objects with some manual syntax
>> modifications.
>>
>>
> thats the easy part.
>
> now what about the massive code base of pl/sql and triggers he mentioned ?
>
>
Yeah, we need to rewrite the business logic if there are any un-supported
features like autonomous transactions, packages, nested procedures, e.t.c.

Regards,
Dinesh
manojadinesh.blogspot.com


>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[CS DBA]

On 07/08/2015 02:20 PM, John R Pierce wrote:
> On 7/8/2015 1:16 PM, dinesh kumar wrote:
>> We recently done the similar migration for one of our customer. We
>> used all opensource tools to achieve this migration process.
>>
>> We used Pentaho Data Integration tool for doing Online DB migration,
>> which took minimal downtime with CDC{Change Data Capture} approach.
>> Also, we used Ora2Pg tool to migrate the DB objects with some manual
>> syntax modifications.
>>
>
> thats the easy part.
>
> now what about the massive code base of pl/sql and triggers he
> mentioned ?
>
>
>
Have you considered using ora2pg?
http://ora2pg.darold.net/

We've done several client migrations with it, quite successfully



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[John R Pierce]

On 7/8/2015 1:16 PM, dinesh kumar wrote:
We recently done the similar migration for one of our customer. We 
used all opensource tools to achieve this migration process.


We used Pentaho Data Integration tool for doing Online DB migration, 
which took minimal downtime with CDC{Change Data Capture} approach. 
Also, we used Ora2Pg tool to migrate the DB objects with some manual 
syntax modifications.




thats the easy part.

now what about the massive code base of pl/sql and triggers he mentioned ?



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[dinesh kumar]

Hi,

We recently done the similar migration for one of our customer. We used all
opensource tools to achieve this migration process.

We used Pentaho Data Integration tool for doing Online DB migration, which
took minimal downtime with CDC{Change Data Capture} approach. Also, we used
Ora2Pg tool to migrate the DB objects with some manual syntax modifications.

Regards,
Dinesh
manojadinesh.blogspot.com

On Wed, Jul 8, 2015 at 12:24 PM, Tim Clotworthy <
tclotwor...@bluestonelogic.com> wrote:

> Hello,
>
> I have a customer that is about to undertake a migration of an Oracle 11g
> database to PostgreSQL 9.x (exact version to be determined). I am talking
> not only of the migration of schemas and data, but also of a substantial
> codebase of Pl/SQL stored procedures, as well as many triggers.
>
> I don't think they know yet what they are up against. Everything I have
> read is that this is a very substantial effort. At this stage, they would
> be particularly interested in realistic and practical information on how
> to
> estimate the effort required as well as any best-practices or guidance on
> transition strategies.
>
> I have found official documentation on the PostgreSQL site for porting
> Pl/SQL to PL/pgSQL. This is excellent technical documentation. However,
> there success will require that they are well prepared realistically
> understanding the scope of the effor they are asbout to undertake.
>
> Thanks for any response!
>

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[John McKown]

On Wed, Jul 8, 2015 at 2:24 PM, Tim Clotworthy <
tclotwor...@bluestonelogic.com> wrote:

> Hello,
>
> I have a customer that is about to undertake a migration of an Oracle 11g
> database to PostgreSQL 9.x (exact version to be determined). I am talking
> not only of the migration of schemas and data, but also of a substantial
> codebase of Pl/SQL stored procedures, as well as many triggers.
>
> I don't think they know yet what they are up against. Everything I have
> read is that this is a very substantial effort. At this stage, they would
> be particularly interested in realistic and practical information on how
> to
> estimate the effort required as well as any best-practices or guidance on
> transition strategies.
>
> I have found official documentation on the PostgreSQL site for porting
> Pl/SQL to PL/pgSQL. This is excellent technical documentation. However,
> there success will require that they are well prepared realistically
> understanding the scope of the effor they are asbout to undertake.
>
> Thanks for any response!
>

​Why are they converting?

Would EnterpriseDB (a commercial version of PostgreSQL which has extensions
to make it a "drop in" replacement for Oracle) be a possibility?
http://www.enterprisedb.com/solutions/oracle-compatibility-technology
​


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Tim Clarke]

This is almost exactly what we did around 8 years ago; obviously the
version numbers have changed. The reason we chose Postgres was the
enormous similarity between the two languages plus the overwhelming ROI
on the migration; my CEO had a spontaneous nosebleed when the Oracle
licensing costs were revealed one year.

Migration requires some thought, investigation, a clear plan and testing
- but I'm probably preaching to the converted.

We did it, we've never looked back and have had at least 8 long very
happy, stable and productive years and are looking forward to many more
(raises a glass to the developers and maintainers).

Tim Clarke

On 08/07/15 20:24, Tim Clotworthy wrote:
> Hello, 
>  
> I have a customer that is about to undertake a migration of an Oracle 11g 
> database to PostgreSQL 9.x (exact version to be determined). I am talking 
> not only of the migration of schemas and data, but also of a substantial 
> codebase of Pl/SQL stored procedures, as well as many triggers. 
>  
> I don't think they know yet what they are up against. Everything I have 
> read is that this is a very substantial effort. At this stage, they would 
> be particularly interested in realistic and practical information on
> how to 
> estimate the effort required as well as any best-practices or guidance on 
> transition strategies. 
>  
> I have found official documentation on the PostgreSQL site for porting 
> Pl/SQL to PL/pgSQL. This is excellent technical documentation. However, 
> there success will require that they are well prepared realistically 
> understanding the scope of the effor they are asbout to undertake. 
>  
> Thanks for any response! 

[GENERAL] Oracle to PostgreSQL Migration - Need Information

[Tim Clotworthy]

Hello,

I have a customer that is about to undertake a migration of an Oracle 11g
database to PostgreSQL 9.x (exact version to be determined). I am talking
not only of the migration of schemas and data, but also of a substantial
codebase of Pl/SQL stored procedures, as well as many triggers.

I don't think they know yet what they are up against. Everything I have
read is that this is a very substantial effort. At this stage, they would
be particularly interested in realistic and practical information on how to
estimate the effort required as well as any best-practices or guidance on
transition strategies.

I have found official documentation on the PostgreSQL site for porting
Pl/SQL to PL/pgSQL. This is excellent technical documentation. However,
there success will require that they are well prepared realistically
understanding the scope of the effor they are asbout to undertake.

Thanks for any response!

[GENERAL] Oracle to PostgreSQL migration

[sridhar bamandlapally]

Hi

With minimal application code changes we are able to migrate database from
Oracle to PostgreSQL successfully (including performance issues) on
production

This we did on high read intensive database sized 900+GB

Thanks to PostgreSQL

Thanks
Sridhar BN

Re: [GENERAL] Oracle to PostgreSQL replication

[Albe Laurenz]

Sameer Kumar wrote:
> I guess I will be exploring more on oracle foreign data wrapper.
> 
> Has anyone tried using oracle_fdw with Oracle RAC? I am wondering how would 
> it handle failovers.

I have not tried it, but it should work as follows:

- You'll have to use a connect string that is correctly defined for
  "transparent application failover", see
  http://docs.oracle.com/cd/E11882_01/network.112/e10836/advcfg.htm#NETAG455

- Failover will happen automatically.

- SELECTs should be able to complete without error message.

- INSERT, UPDATE and DELETE will fail with an error message and
  will have to be retried.  I did not code oracle_fdw to support
  TAF for DML statements.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL replication

[Thomas Kellerer]

Rajni Baliyan, 09.05.2014 09:46:
> I downloaded symmetricDS but I did not found any installer 
> (symmetric-pro-x.x.x.jar)
> Can any one please help me in this

This might help: 
http://www.symmetricds.org/doc/3.5/html/tutorial.html#tutorial-install






-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL replication

[Rajni Baliyan]

I downloaded symmetricDS but I did not found any installer
(symmetric-pro-x.x.x.jar)
Can any one please help me in this

Thanks

Best Regards,

*Rajni Baliyan | Database - Consultant*
*ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M:+65 87294147 T: +65 6438 3504 | www.ashnik.com

*[image: icons]*

[image: Email patch] <http://www.ashnik.com/>
This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).


On Fri, May 9, 2014 at 4:11 AM, Anand Kumar, Karthik <
karthik.anandku...@classmates.com> wrote:

>  We use symmetricDS for this. Works pretty well.
>
>  http://www.symmetricds.org/
>
>
>   From: Serge Fonville 
> Date: Wednesday, May 7, 2014 at 2:49 AM
> To: Geoff Montee 
> Cc: Sameer Kumar , PostgreSQL General Discussion
> Forum 
> Subject: Re: [GENERAL] Oracle to PostgreSQL replication
>
>   Hi,
>
>  I need to setup a replication process for continuously replicating
>> changes happening in an Oracle Database to a PostgreSQL database.
>>
>> My Oracle Database is version 11.2 and setup as a cluster with RAC
>> My Postgres database version is 9.2
>>
>> Oracle Database is running in Solaris and PostgreSQL is running on RHEL.
>>
>> Is there any commercial or open source tool available to achieve this?
>>
>
>  How about EnterpriseDB XDB 
> replication<http://www.enterprisedb.com/products-services-training/products/complementary-enterprisedb-products/xdb-replication-server>
> ?
>
> Kind regards/met vriendelijke groet,
>
>  Serge Fonville
>
>  http://www.sergefonville.nl
>
>
> 2014-05-07 11:44 GMT+02:00 Geoff Montee :
>
>>
>>  On Wed, May 7, 2014 at 12:31 AM, Sameer Kumar 
>> wrote:
>>
>>>  Hi,
>>>
>>>
>>>  I need to setup a replication process for continuously replicating
>>> changes happening in an Oracle Database to a PostgreSQL database.
>>>
>>>
>>>  My Oracle Database is version 11.2 and setup as a cluster with RAC
>>> My Postgres database version is 9.2
>>>
>>>  Oracle Database is running in Solaris and PostgreSQL is running on
>>> RHEL.
>>>
>>>  Is there any commercial or open source tool available to achieve this?
>>>
>>>
>>>
>>  Continuent's Tungsten Replicator apparently offers Oracle to MySQL
>> replication. There's a wiki page that suggests PostgreSQL support was in
>> development at one time. I'm not sure how far they got, or if they are
>> still working on it.
>>
>>  http://www.continuent.com/solutions/replication
>>
>>  https://wiki.postgresql.org/wiki/Tungsten
>>
>>  Geoff Montee
>>
>
>

Re: [GENERAL] Oracle to PostgreSQL replication

[Anand Kumar, Karthik]

We use symmetricDS for this. Works pretty well.

http://www.symmetricds.org/


From: Serge Fonville mailto:serge.fonvi...@gmail.com>>
Date: Wednesday, May 7, 2014 at 2:49 AM
To: Geoff Montee mailto:geoff.mon...@gmail.com>>
Cc: Sameer Kumar mailto:sameer.ku...@ashnik.com>>, 
PostgreSQL General Discussion Forum 
mailto:pgsql-general@postgresql.org>>
Subject: Re: [GENERAL] Oracle to PostgreSQL replication

Hi,

I need to setup a replication process for continuously replicating changes 
happening in an Oracle Database to a PostgreSQL database.

My Oracle Database is version 11.2 and setup as a cluster with RAC
My Postgres database version is 9.2

Oracle Database is running in Solaris and PostgreSQL is running on RHEL.

Is there any commercial or open source tool available to achieve this?

How about EnterpriseDB XDB 
replication<http://www.enterprisedb.com/products-services-training/products/complementary-enterprisedb-products/xdb-replication-server>?

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl


2014-05-07 11:44 GMT+02:00 Geoff Montee 
mailto:geoff.mon...@gmail.com>>:

On Wed, May 7, 2014 at 12:31 AM, Sameer Kumar 
mailto:sameer.ku...@ashnik.com>> wrote:
Hi,


I need to setup a replication process for continuously replicating changes 
happening in an Oracle Database to a PostgreSQL database.


My Oracle Database is version 11.2 and setup as a cluster with RAC
My Postgres database version is 9.2

Oracle Database is running in Solaris and PostgreSQL is running on RHEL.

Is there any commercial or open source tool available to achieve this?



Continuent's Tungsten Replicator apparently offers Oracle to MySQL replication. 
There's a wiki page that suggests PostgreSQL support was in development at one 
time. I'm not sure how far they got, or if they are still working on it.

http://www.continuent.com/solutions/replication

https://wiki.postgresql.org/wiki/Tungsten

Geoff Montee

Re: [GENERAL] Oracle to PostgreSQL replication

[tuanhoanganh]

You can test with SymmetricDS (www.*symmetricds*.org)


On Thu, May 8, 2014 at 10:35 PM, tuanhoanganh  wrote:

> You can test with SymmetricDS (www.*symmetricds*.org)
>
>
> On Thu, May 8, 2014 at 12:53 PM, Sameer Kumar wrote:
>
>>
>> Thanks alot everyone!
>>
>> I guess I will be exploring more on oracle foreign data wrapper.
>>
>> Has anyone tried using oracle_fdw with Oracle RAC? I am wondering how
>> would it handle failovers.
>>
>>
>

Re: [GENERAL] Oracle to PostgreSQL replication

[Sameer Kumar]

Thanks alot everyone!

I guess I will be exploring more on oracle foreign data wrapper.

Has anyone tried using oracle_fdw with Oracle RAC? I am wondering how would
it handle failovers.

Re: [GENERAL] Oracle to PostgreSQL replication

[Serge Fonville]

Hi,

I need to setup a replication process for continuously replicating changes
> happening in an Oracle Database to a PostgreSQL database.
>
> My Oracle Database is version 11.2 and setup as a cluster with RAC
> My Postgres database version is 9.2
>
> Oracle Database is running in Solaris and PostgreSQL is running on RHEL.
>
> Is there any commercial or open source tool available to achieve this?
>

How about EnterpriseDB XDB
replication
?

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl


2014-05-07 11:44 GMT+02:00 Geoff Montee :

>
> On Wed, May 7, 2014 at 12:31 AM, Sameer Kumar wrote:
>
>> Hi,
>>
>>
>> I need to setup a replication process for continuously replicating
>> changes happening in an Oracle Database to a PostgreSQL database.
>>
>>
>> My Oracle Database is version 11.2 and setup as a cluster with RAC
>> My Postgres database version is 9.2
>>
>> Oracle Database is running in Solaris and PostgreSQL is running on RHEL.
>>
>> Is there any commercial or open source tool available to achieve this?
>>
>>
>>
> Continuent's Tungsten Replicator apparently offers Oracle to MySQL
> replication. There's a wiki page that suggests PostgreSQL support was in
> development at one time. I'm not sure how far they got, or if they are
> still working on it.
>
> http://www.continuent.com/solutions/replication
>
> https://wiki.postgresql.org/wiki/Tungsten
>
> Geoff Montee
>

Re: [GENERAL] Oracle to PostgreSQL replication

[Geoff Montee]

On Wed, May 7, 2014 at 12:31 AM, Sameer Kumar wrote:

> Hi,
>
>
> I need to setup a replication process for continuously replicating changes
> happening in an Oracle Database to a PostgreSQL database.
>
>
> My Oracle Database is version 11.2 and setup as a cluster with RAC
> My Postgres database version is 9.2
>
> Oracle Database is running in Solaris and PostgreSQL is running on RHEL.
>
> Is there any commercial or open source tool available to achieve this?
>
>
>
Continuent's Tungsten Replicator apparently offers Oracle to MySQL
replication. There's a wiki page that suggests PostgreSQL support was in
development at one time. I'm not sure how far they got, or if they are
still working on it.

http://www.continuent.com/solutions/replication

https://wiki.postgresql.org/wiki/Tungsten

Geoff Montee

Re: [GENERAL] Oracle to PostgreSQL replication

[Albe Laurenz]

Sameer Kumar wrote:
> I need to setup a replication process for continuously replicating changes 
> happening in an Oracle
> Database to a PostgreSQL database.
> 
> 
> My Oracle Database is version 11.2 and setup as a cluster with RAC
> My Postgres database version is 9.2
> 
> Oracle Database is running in Solaris and PostgreSQL is running on RHEL.
> 
> Is there any commercial or open source tool available to achieve this?
> 
> I was wondering has anyone used foreign data  wrapper or

There is no ready-made solution for this from the PostgreSQL side.
You could check with Oracle if they provide something like that
with their "Golden Gate".

What you could do is to have a trigger record all changes to the Oracle
table in a separate log table and regularly run a program that pulls those
changes from the log table and applies them to a PostgreSQL table,
deleting the log entries as it goes.

You could write such a thing as PostgreSQL function using oracle_fdw,
but you need PostgreSQL 9.3 if you want to update Oracle data that way.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Oracle to PostgreSQL replication

[Sameer Kumar]

Hi,


I need to setup a replication process for continuously replicating changes
happening in an Oracle Database to a PostgreSQL database.


My Oracle Database is version 11.2 and setup as a cluster with RAC
My Postgres database version is 9.2

Oracle Database is running in Solaris and PostgreSQL is running on RHEL.

Is there any commercial or open source tool available to achieve this?

I was wondering has anyone used foreign data  wrapper or

Best Regards,

*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

M: *+65 8110 0350*  T: +65 6438 3504 | www.ashnik.com

*[image: icons]*



[image: Email patch] 



This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).

Re: [GENERAL] Oracle to PostgreSQL transition?

[Martín Marqués]

2013/4/5 Robert Treat :
>
> Yeah, it's worth reading through those links to get an idea of things;
> you'll find much less literature (and tools) for MSSQL, but the
> principals are mostly the same. One thing to decide on is if you are
> going to port applications wholesale, or try to run some kind of
> hybrid oracle/mssql -> postgres mix. If it's the latter, you'll
> probably need to write your own tools; at least we've always done that
> as we've never found anything that worked with both Oracle and MSSQL
> into Postgres reliably; but really it shouldn't be too difficult;
> basically just ETL or some home brew replication scripting to glue
> things together.

I gave some training a few years ago for devs that were migrating from
SQLServer to PostgreSQL and they had a bunch of SP, some of easy
resolution, and others that where impossible with PG functions written
in plpgsql. They used CURSORS a lot to simulate what they did with
SQLServer (there was a better solution, but time was crucial, and they
decided for that approach)


--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL transition?

[Joshua D. Drake]

On 04/05/2013 08:03 AM, Robert Treat wrote:


On Fri, Apr 5, 2013 at 2:12 AM, Szymon Guz  wrote:

On 4 April 2013 16:16, Roy Anderson  wrote:


Hey all,

We have a very robust Oracle and SQL Server presence at work but we're
looking to farm out some of the load to PostgreSQL to limit costs. I'm
curious if there are any DBAs out there who have gone down this route
before. Any tips, tricks, failures, successes, etc.? I would just like to
hear some first-hand commentary on this topic.

Thank you so much!


Yes, we have done this for customers on more than one occasion. The 
single biggest tip I can give you is to remember that PostgreSQL is not 
Oracle. You are porting to PostgreSQL, don't expect it to act like 
Oracle. As long as you keep that in mind, and you aren't running RAC 
things are pretty straightforward. They can also be quite a bit easier 
(and cheaper) to get a lot of external functionality such as replication.


Sincerely,

Joshua D. Drake

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC
@cmdpromptinc - 509-416-6579


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL transition?

[Shaun Thomas]

On 04/04/2013 09:16 AM, Roy Anderson wrote:


We have a very robust Oracle and SQL Server presence at work but we're
looking to farm out some of the load to PostgreSQL to limit costs.


You can do what we did. EnterpriseDB has built-in Oracle compatibility 
through a series of plugins, patches, and wrappers. You can generally 
convert with only a few small changes.


From there, you can take your time converting to PG syntax and go 
through more iterations in your dev and staging environments.


If it's early enough in the lifetime of your platform, you could also 
just convert everything at once. That's not always an option for 
everyone though. :)


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL transition?

[Robert Treat]

On Fri, Apr 5, 2013 at 2:12 AM, Szymon Guz  wrote:
> On 4 April 2013 16:16, Roy Anderson  wrote:
>>
>> Hey all,
>>
>> We have a very robust Oracle and SQL Server presence at work but we're
>> looking to farm out some of the load to PostgreSQL to limit costs. I'm
>> curious if there are any DBAs out there who have gone down this route
>> before. Any tips, tricks, failures, successes, etc.? I would just like to
>> hear some first-hand commentary on this topic.
>>
>> Thank you so much!
>>
>> Roy
>
>
> Hi Roy,
> maybe this would be helpful:
>
> http://wiki.postgresql.org/wiki/PostgreSQL_for_Oracle_DBAs
> http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
> http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle
>

Yeah, it's worth reading through those links to get an idea of things;
you'll find much less literature (and tools) for MSSQL, but the
principals are mostly the same. One thing to decide on is if you are
going to port applications wholesale, or try to run some kind of
hybrid oracle/mssql -> postgres mix. If it's the latter, you'll
probably need to write your own tools; at least we've always done that
as we've never found anything that worked with both Oracle and MSSQL
into Postgres reliably; but really it shouldn't be too difficult;
basically just ETL or some home brew replication scripting to glue
things together.

Robert Treat
conjecture: xzilla.net
consulting: omniti.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL transition?

[Michael Paquier]

On Thu, Apr 4, 2013 at 11:16 PM, Roy Anderson wrote:

> Hey all,
>
> We have a very robust Oracle and SQL Server presence at work but we're
> looking to farm out some of the load to PostgreSQL to limit costs. I'm
> curious if there are any DBAs out there who have gone down this route
> before. Any tips, tricks, failures, successes, etc.? I would just like to
> hear some first-hand commentary on this topic.
>
Here is a tool that can be used to migrate an Oracle DB into a Postgres DB:
https://github.com/darold/ora2pg

Hope it is useful.
-- 
Michael

Re: [GENERAL] Oracle to PostgreSQL transition?

[Szymon Guz]

On 4 April 2013 16:16, Roy Anderson  wrote:

> Hey all,
>
> We have a very robust Oracle and SQL Server presence at work but we're
> looking to farm out some of the load to PostgreSQL to limit costs. I'm
> curious if there are any DBAs out there who have gone down this route
> before. Any tips, tricks, failures, successes, etc.? I would just like to
> hear some first-hand commentary on this topic.
>
> Thank you so much!
>
> Roy
>

Hi Roy,
maybe this would be helpful:

http://wiki.postgresql.org/wiki/PostgreSQL_for_Oracle_DBAs
http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle

regards
Szymon

[GENERAL] Oracle to PostgreSQL transition?

[Roy Anderson]

Hey all,

We have a very robust Oracle and SQL Server presence at work but we're
looking to farm out some of the load to PostgreSQL to limit costs. I'm
curious if there are any DBAs out there who have gone down this route
before. Any tips, tricks, failures, successes, etc.? I would just like to
hear some first-hand commentary on this topic.

Thank you so much!

Roy

[GENERAL] Oracle to PostgreSQL replication with Goldengate

[Jayadevan M]

Hello all,
GoldenGate added PostgreSQL as a target database for replication. I tried
setting it up, and not finding any tutorial, put together a how to here -
http://jayadevanm.wordpress.com/2012/11/07/goldengate-replication-from-oracle-to-postgresql/
I think near real-time replication might have quite a few cases - such as
trying out the application on PostgreSQL before really cutting over from an
Oracle database, may be running reports off PostgreSQL and so on.
Regards,
Jayadevan

Re: [GENERAL] Oracle to PostgreSQL

[John R Pierce]

Thomas Kellerer wrote:


change number to numeric, and varchar2 to char and you'll likely be 
good.





My guess would have been that varchar is the equivalent to varchar2

char does a blank padding and is different in behaviour to varchar (as 
it is in Oracle)



geez, yeah, what you said!BTDT, and had the t-shirt.i think ill 
have another beer now.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL

[Thomas Kellerer]

John R Pierce wrote on 16.03.2009 00:41:

Greenhorn wrote:
Unfortunately When I execute the above files using \i I am getting 
errors like


ERROR:  syntax error at or near "("
LINE 4:   SUB_TYPE  NUMBER(3)   NOT NULL,

Obviously 'number' is not PostgreSQL data type so is 'varchar2'.  What
is your suggestion to create these tables in PostgreSQL?  Should I
pre-process using Sed/Awk to find/replace the field type first?  Any
recommendation is greatly appreciated :)
  


change number to numeric, and varchar2 to char and you'll likely be good.




My guess would have been that varchar is the equivalent to varchar2

char does a blank padding and is different in behaviour to varchar (as it is in 
Oracle)


Thomas





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle to PostgreSQL

[John R Pierce]

Greenhorn wrote:

Unfortunately When I execute the above files using \i I am getting errors like

ERROR:  syntax error at or near "("
LINE 4:   SUB_TYPE  NUMBER(3)   NOT NULL,

Obviously 'number' is not PostgreSQL data type so is 'varchar2'.  What
is your suggestion to create these tables in PostgreSQL?  Should I
pre-process using Sed/Awk to find/replace the field type first?  Any
recommendation is greatly appreciated :)
  


change number to numeric, and varchar2 to char and you'll likely be good.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Oracle to PostgreSQL

[Greenhorn]

Hi,

I have almost 1300 files from Oracle (no access to oracle server).  I
have to create the tables and data as follows.

-- start script

-- file name:  aa_asset_type.sql

CREATE TABLE AS_ASSET_TYPE
(
  MAIN_TYPE CHAR(3) NOT NULL,
  SUB_TYPE  NUMBER(3)   NOT NULL,
  DESCRIPTION   VARCHAR2(25)DEFAULT ' '
   NOT NULL,
  SERVICE_SCHEDULE  VARCHAR2(8) DEFAULT ' '
   NOT NULL,
  PRODUCT_CODE  CHAR(4) DEFAULT ' '
   NOT NULL
);

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON
GENADMIN.AS_ASSET_TYPE TO PUBLIC;

INSERT INTO AS_ASSET_TYPE ( MAIN_TYPE, SUB_TYPE, DESCRIPTION, SERVICE_SCHEDULE,
PRODUCT_CODE ) VALUES (
'000', 0, ' ', ' ', '');
INSERT INTO AS_ASSET_TYPE ( MAIN_TYPE, SUB_TYPE, DESCRIPTION, SERVICE_SCHEDULE,
PRODUCT_CODE ) VALUES (
'001', 0, ' ', ' ', '');
INSERT INTO AS_ASSET_TYPE ( MAIN_TYPE, SUB_TYPE, DESCRIPTION, SERVICE_SCHEDULE,
PRODUCT_CODE ) VALUES (
'100', 0, 'BUILDINGS', ' ', '');

-- file name:  as_master.sql

CREATE TABLE AS_MASTER
(
  ASSET_NO VARCHAR2(16) DEFAULT ' '
   NOT NULL,
  FUND CHAR(1)  NOT NULL,
  FINAL_ACCOUNT_NO NUMBER(6)NOT NULL,
  ITEM_NO  NUMBER(6)NOT NULL,
  SUB_NO   NUMBER(6)NOT NULL,
  TYPE NUMBER(6)DEFAULT 0
   NOT NULL,
  SUB_TYPE NUMBER(6)DEFAULT 0
   NOT NULL,
  LOCATION NUMBER(6)DEFAULT 0
   NOT NULL,
  SUB_LOCATION NUMBER(6)DEFAULT 0
   NOT NULL,
  LEVEL_A  VARCHAR2(4)  DEFAULT ' '
   NOT NULL,
  LEVEL_B  VARCHAR2(4)  DEFAULT ' '
   NOT NULL,
  LEVEL_C  VARCHAR2(4)  DEFAULT ' '
   NOT NULL,
  LEVEL_D  VARCHAR2(4)  DEFAULT ' '
   NOT NULL,
  LEVEL_E  VARCHAR2(4)  DEFAULT ' '
   NOT NULL,
  OPENING_ACQ_VALUENUMBER(15,2) DEFAULT 0
   NOT NULL,
  OPENING_ACQ_QTY  NUMBER(12)   DEFAULT 0
   NOT NULL,
  WITHDRAWAL_VALUE NUMBER(15,2) DEFAULT 0
   NOT NULL,
  WITHDRAWAL_QTY   NUMBER(12)   DEFAULT 0
   NOT NULL,
  PROFIT_LOSS_VALUENUMBER(15,2) DEFAULT 0
   NOT NULL,
  DEPREC_VALUE NUMBER(15,2) DEFAULT 0
   NOT NULL,
  PROFIT_LOSS_YTD  NUMBER(15,2) DEFAULT 0
   NOT NULL,
  DEPREC_YTD   NUMBER(15,2) DEFAULT 0
   NOT NULL,
  DEPREC_LAST_MONTHNUMBER(6)DEFAULT 0
   NOT NULL,
  DEPREC_LAST_YEAR NUMBER(6)DEFAULT 0
   NOT NULL,
  DEPREC_METHODCHAR(1)  DEFAULT ' '
   NOT NULL,
  DEPREC_RATE_2NUMBER(15,6) DEFAULT 0
   NOT NULL,
  DEPREC_ROUND_FACTOR  NUMBER(6)DEFAULT 0
   NOT NULL,
  DEPREC_FREQUENCY CHAR(1)  DEFAULT ' '
   NOT NULL,
  DEPREC_MINIMUM_VAL   NUMBER(12)   DEFAULT 0
   NOT NULL,
  DEPREC_TYPE  NUMBER(6)DEFAULT 0
   NOT NULL,
  DEPREC_FOLIO_DR  VARCHAR2(8)  DEFAULT ' '
   NOT NULL,
  DEPREC_FOLIO_CR  VARCHAR2(8)  DEFAULT ' '
   NOT NULL,
  INSURANCE_VALUE  NUMBER(12)   DEFAULT 0
   NOT NULL,
  INSURANCE_CATEGORY   VARCHAR2(2)  DEFAULT ' '
   NOT NULL,
  INSURANCE_POLICY VARCHAR2(10) DEFAULT ' '
   NOT NULL,
  INSURANCE_CODE   CHAR(1)  DEFAULT ' '
   NOT NULL,
  ASSET_FILE_NOVARCHAR2(10) DEFAULT ' '
   NOT NULL,
  PROPERTY_REF VARCHAR2(20) DEFAULT ' '
   NOT NULL,
  LAST_AUDIT_DATE  DATE NULL,
  LAST_AUDIT_PAGE  NUMBER(6)DEFAULT 0
   NOT NULL,
  DESCRIPTION_1VARCHAR2(40) DEFAULT ' '
   NOT NULL,
  DESCRIPTION_2VARCHAR2(40) DEFAULT ' '
   NOT NULL
);


GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON
GENADMIN.AS_MASTER TO PUBLIC;

ALTER TABLE GENADMIN.AS_MASTER ADD (
  PRIMARY KEY
 (ASSET_NO));

INSERT INTO AS_MASTER ( ASSET_NO, FUND, FINAL_ACCOUNT_NO, ITEM_NO,
SUB_NO, TYPE, SUB_TYPE,
LOCATION, SUB_LOCATION, LEVEL_A, LEVEL_B, LEVEL_C, LEVEL_D, LEVEL_E,
OPENING_ACQ_VALUE,
OPENING_ACQ_QTY, WITHDRAWAL_VALUE, WITHDRAWAL_QTY, PROFIT_LOSS_VALUE,
DEPREC_VALUE, PROFIT_LOSS_YTD,
DEPREC_YTD, DEPREC_LAST_MONTH, DEPREC_LAST_YEAR, DEPREC_METHOD,
DEPREC_RATE_2, DEPREC_ROUND_FACTOR,
DEPREC_FREQUENCY, DEPREC_MINIMUM_VAL, DEPREC_TYPE, DEPREC_FOLIO_DR,
DEPREC_FOLIO_CR,
INSURANCE_VALUE, INSURANCE_CATEGORY, INSURANCE_POLICY, INSURANCE_CODE,
ASSET_FILE_NO, PROPERTY_REF,
LAST_AUDIT_DATE, LAST_AUDIT_PAGE, DESCRIPTION_1, DE

Re: [GENERAL] "oracle to postgresql" conversion

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/09/07 14:53, Chris Fischer wrote:
> All of Oracle's (non-float) number types are variable size
> numbers with an ordinal and a mantissa.  This makes Oracle number
> very efficient for smaller values as compared to fixed size
> integers, but less efficient with larger values.  NUMBER has a
> maximum precision of 38 digits with a scale of -84 to +127.
> NUMBER consumes between 1 and 22 bytes on disk.  It is typical to
> specify a NUMBER with (p, s).  In the absence of definition,
> precision of 38 and scale indeterminate will be assumed.
> 
> The exception to this are IEEE floating point number types which
> are a fixed size regardless of value.
> 
> Summary: Oracle has no fixed length equivlents to tinyint,
> smallint, int or bigint from other databases and can either store
> these values more or less efficiently than those databases with
> fixed length integer types.

Wow  Didn't believe you (Oracle couldn't be *that* lame, could
it?), so I Googled.

According to Table 12-1 of this web page, Oracle will silently
truncate your numbers.  There are no scalar data types
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c13datyp.htm


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF8dDjS9HxQb37XmcRArCMAKDAFuUM2V804Zjdurr6eemqPyHHOwCg1oGk
8RxOTImJVBUqdBhHK6tezkA=
=ibbT
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] "oracle to postgresql" conversion

[Chris Fischer]

All of Oracle's (non-float) number types are variable size numbers with an 
ordinal and a mantissa.  This makes Oracle number very efficient for smaller 
values as compared to fixed size integers, but less efficient with larger 
values.  NUMBER has a maximum precision of 38 digits with a scale of -84 to 
+127.  NUMBER consumes between 1 and 22 bytes on disk.  It is typical to 
specify a NUMBER with (p, s).  In the absence of definition, precision of 38 
and scale indeterminate will be assumed.

The exception to this are IEEE floating point number types which are a fixed 
size regardless of value.

Summary: Oracle has no fixed length equivlents to tinyint, smallint, int or 
bigint from other databases and can either store these values more or less 
efficiently than those databases with fixed length integer types.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe
Sent: Friday, March 09, 2007 1:58 PM
To: Devrim GÜNDÜZ
Cc: Shane Ambler; Kevin Hunter; [EMAIL PROTECTED]; PostgreSQL General List
Subject: Re: [GENERAL] "oracle to postgresql" conversion

On Thu, 2007-03-08 at 16:05, Devrim GÜNDÜZ wrote:
> Hi,
> 
> On Fri, 2007-03-09 at 05:21 +1030, Shane Ambler wrote:
> > NUMBER is Oracle's version of NUMERIC - Oracle will use both but 
> > probably only Oracle will use NUMBER.
> 
> Really? I thought Oracle's NUMBER ~ PostgreSQL's (BIG)INT?

Not sure.  It let me assign a precision to it, so I figured it wasn't int 
based.  In fact, it accepts precision up to 38, just like numeric, and it 
accepts non-decimal portions, i.e.:

number(20,4);

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] "oracle to postgresql" conversion

[Scott Marlowe]

On Thu, 2007-03-08 at 16:05, Devrim GÜNDÜZ wrote:
> Hi,
> 
> On Fri, 2007-03-09 at 05:21 +1030, Shane Ambler wrote:
> > NUMBER is Oracle's version of NUMERIC - Oracle will use both but 
> > probably only Oracle will use NUMBER. 
> 
> Really? I thought Oracle's NUMBER ~ PostgreSQL's (BIG)INT?

Not sure.  It let me assign a precision to it, so I figured it wasn't
int based.  In fact, it accepts precision up to 38, just like numeric,
and it accepts non-decimal portions, i.e.:

number(20,4);

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] "oracle to postgresql" conversion

[Devrim GÜNDÜZ]

Hi,

On Fri, 2007-03-09 at 05:21 +1030, Shane Ambler wrote:
> NUMBER is Oracle's version of NUMERIC - Oracle will use both but 
> probably only Oracle will use NUMBER. 

Really? I thought Oracle's NUMBER ~ PostgreSQL's (BIG)INT?

/me looks at Oracle docs again.  
-- 
Devrim GÜNDÜZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/




signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] "oracle to postgresql" conversion

[Scott Marlowe]

On Tue, 2007-03-06 at 11:01, [EMAIL PROTECTED] wrote:
> I'm a newbie in Oracle and postgreSQL,
> i'm need to translate the following script (in Oracle) to postgreSQL :
> 
> rem Autorisation des lignes vides :
> set sqlbl on

You don't need this, postgresql doesn't choke on extra lines.

> rem Initialisation du timer :
> set timing on

-- Change this to \timing if you're gonna use psql to run the script:
\timing

> rem Creation de la table :

becomes

-- Creation de la table :

Now, we need to use real SQL 99 types here, or specific postgresql
types.  And don't quote unless you need to.  PostgreSQL folds to lower
case, not upper case, so if you quote upper case here, you'll always
have to quote in the future.  Better to just not quote, in my humble
opinion.

So, "LEPAPE" will become lepape
VARCHAR2(16) will become varchar(16)
NUMBER will become either 
   decimal or numeric
NOT NULL is still NOT NULL
and the check constraint will look the same too.  again unless you
require upper case, leave the SYS_C009967 lower case, and better yet,
give it a useful name, like lepape_measure_check

CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,

CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))



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

   http://www.postgresql.org/docs/faq

Re: [GENERAL] "oracle to postgresql" conversion

[Shane Ambler]

Kevin Hunter wrote:

On 06 Mar 2007 at 9:01a -0800, g.c[ altudela ] wrote:

I'm a newbie in Oracle and postgreSQL,
i'm need to translate the following script (in Oracle) to postgreSQL :

rem Autorisation des lignes vides :
set sqlbl on

rem Initialisation du timer :
set timing on

rem Creation de la table :


I would say you can remove these. rem is just a comment Which can be 
changed to -- . The two set lines are session settings that you won't miss.


You may want to consider starting with your CREATE DATABASE command and 
make sure you have a suitable ENCODING setting.


Start by adding -
CREATE SCHEMA "LEPAPE";

You may want to add "LEPAPE" to your search path so you don't need to 
have it before everything.

http://www.postgresql.org/docs/8.2/interactive/ddl-schemas.html#DDL-SCHEMAS-PATH
can explain that further.


CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,

CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))


Change VARCHAR2 to VARCHAR
Change NUMBER to NUMERIC
Change CHECK(measure to CHECK("MEASURE"

VARCHAR2 is an Oracle type that replaces VARCHAR. Not sure why but my 
first guess would be it works with multibyte character sets which is why 
I suggest checking your ENCODING before you start.


NUMBER is Oracle's version of NUMERIC - Oracle will use both but 
probably only Oracle will use NUMBER.


The CHECK change is to do with identifiers which I point you to later.

You may want to bookmark http://www.ss64.com/orasyntax/datatypes.html
and http://www.postgresql.org/docs/8.2/interactive/datatype.html
as references for future scripts that you may stumble on.


I believe rem translates to '-- ' (the extra space is important)

set translates to '\set'

I do not know what the setting 'sqlbl' does in Oracle.

I'm not entirely sure about the owner bit, specified by "LEPAPE"."..."



The various data types translate to (likely) more standards compliant
names, which you can peruse at

http://www.postgresql.org/docs/8.2/static/datatype.html

(Replace 8.2 with your major version of PostgreSQL.)



Someone may correct me, but I believe that Postgres is not case
sensitive in (terms of column and constraint names) unless you create
them with quotes.  Thus, you could just as easily write

"EXP_ID" VARCHAR2(16) NOT NULL,

as

exp_id VARCHAR(16) NOT NULL


I would say remove all the double quotes, but this will depend on the 
other scripts that have selects and such - it may be easier to leave 
them as is than to update all the scripts you have. If you are typing 
from a book then that won't matter.


I am guessing that this is an Oracle tutorial that you have and you want 
to use postgresql to work through it. This would be a good time to get 
familiar with naming conventions, start by reading -

http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
This should help you to understand why part of the above table 
definition should be changed to CHECK("MEASURE" and you may have some 
similar fixes throughout your scripts.



which would be my personal preference as I like to capitalize SQL
keywords and leave everything as lower case.  (Makes for easier reading
later.)

Kevin


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match




--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

  http://www.postgresql.org/docs/faq

Re: [GENERAL] "oracle to postgresql" conversion

[Jan de Visser]

On Thursday 08 March 2007 11:40:21 am Kevin Hunter wrote:
> I do not know what the setting 'sqlbl' does in Oracle.

SET SQLBLANKLINES ON makes sqlplus not choke on empty lines in your input.

Don't get me started.

jan



-- 
--
Jan de Visser                     [EMAIL PROTECTED]

                Baruk Khazad! Khazad ai-menu!
--

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] "oracle to postgresql" conversion

[Kevin Hunter]

On 06 Mar 2007 at 9:01a -0800, g.c[ altudela ] wrote:

I'm a newbie in Oracle and postgreSQL,
i'm need to translate the following script (in Oracle) to postgreSQL :

rem Autorisation des lignes vides :
set sqlbl on

rem Initialisation du timer :
set timing on

rem Creation de la table :

CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,

CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))


I believe rem translates to '-- ' (the extra space is important)

set translates to '\set'

I do not know what the setting 'sqlbl' does in Oracle.

I'm not entirely sure about the owner bit, specified by "LEPAPE"."..."



The various data types translate to (likely) more standards compliant
names, which you can peruse at

http://www.postgresql.org/docs/8.2/static/datatype.html

(Replace 8.2 with your major version of PostgreSQL.)



Someone may correct me, but I believe that Postgres is not case
sensitive in (terms of column and constraint names) unless you create
them with quotes.  Thus, you could just as easily write

"EXP_ID" VARCHAR2(16) NOT NULL,

as

exp_id VARCHAR(16) NOT NULL

which would be my personal preference as I like to capitalize SQL
keywords and leave everything as lower case.  (Makes for easier reading
later.)

Kevin


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] "oracle to postgresql" conversion

[altudela]

I'm a newbie in Oracle and postgreSQL,
i'm need to translate the following script (in Oracle) to postgreSQL :


rem Autorisation des lignes vides :
set sqlbl on

rem Initialisation du timer :
set timing on

rem Creation de la table :

CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,

CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))



Thanks!


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Oracle to PostgreSQL migration

[Uwe C. Schroeder]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


I think that's been part of pgadmin 2

On Monday 08 December 2003 10:49 pm, Randy Harris wrote:
> I've been scouring the PostgreSQL web site and have found several
> references to a Database Migration Wizard.  I was unable, however, to find
> any details. Would someone be kind enough to explain what and where it is? 
> I need to migrate an Oracle db to PostgreSQL.
>
>
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

- -- 
UC

- --
Open Source Solutions 4U, LLC   2570 Fleetwood Drive
Phone:  +1 650 872 2425 San Bruno, CA 94066
Cell:   +1 650 302 2405 United States
Fax:+1 650 872 2417
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/1nP4jqGXBvRToM4RAtNDAJwOOpIgk1da1DMmC2DvcNONZHk77ACgu2b0
QBvZnGi+n3yvBFPDnu542Zk=
=2xqU
-END PGP SIGNATURE-


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org