Re: Oracle to PostgreSQL Migration.

On Mon, May 29, 2017 at 6:28 PM, Chris Mair <chris(at)1006(dot)org> 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_USER test
> #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.

*TYPE TABLE*

*output after this*
-------------------------------------
C:\ora2pg>ora2pg -c ora2pg.conf
Ora2Pg version: 18.1
Trying to connect to database:
dbi:Oracle:host=<servername>;sid=<mysid>;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=<servername>;sid=<mysid>;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=<servername>;sid=<mysid>;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=<servername>))(CONNECT_DATA=(SID=<mysid>))) 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.