Issue in migrating thrid party database - postgres

anand_gp

Hi,

I am using SQL Developer Version 18.4.0.376.  I am trying to migrate postgresql DB to oracle using Migration Wizard.  Under the "Source Database" option, I need to choose Third Party Database from Connection drop down option.  I am clicking on Add button to add a connection.  I get a list of existing connections.  ->

  I clicked on "postgresTESTdb" which is related to postgresql DB.  If I click on Test, it is successful.  However, if I choose that to add into connections, it doesnt appear in the connections list ->

I also checked "Add Platform", which shows, relevant jar file being used for the connection. 

However, "Available source platform" shows nothing ->

But, I have used that same jar file to create a connection to connect to a postgres database without any issues ->

Can someone help me to know what's going wrong here?  Many Thanks.

Regards,

-Anand

thatJeffSmith-Oracle

No, it means the generic code we have for Drag and Drop and Copy to Oracle is busted for postgresql. We see the issue and will have a fix for 20.1

thatJeffSmith-Oracle

That wizard doesn't support Postgres.

Right now you can drag and drop tables to Oracle or right click on one and say Copy to Oracle.

Formal Postgres migration support is coming later this year.

anand_gp

Hi,

Thanks for your pointers. 

Right click "Copy to Oracle"   ->   This does not work.  Doesn't show any dailog after clicking on Copy to Oracle.

Tried with drag and drop of table and got below error ->

Copied Objects:
pgARUNtest.SuVikas_Core.aadhaar.TABLE.aadhar_subsidy_type
Drop Target: TEST2
Copy DDL: Yes
Replace
Copy Data: Yes
Task failed.

1 tables failed.
    Failed to create Table SuVikas_Core.aadhaar.aadhar_subsidy_type. Rollback sucessful
        ERROR: syntax error at or near "`"
          Position: 15

0 table(s) copied.

And my Postgres ver 12 and PGAdmin version -> 4.16

Thanks.

-Anand

anand_gp

When I try to drag and drop I get below options.  I tried all possible options but of no use.

ERROR Detail -

Copied Objects:
pgARUNtest.SuVikas_Core.aadhaar.TABLE.aadhar_subsidy_type
Drop Target: TEST2
Copy DDL: Yes
Do Not Replace Existing Objects
Copy Data: Yes
Append Existing Objects
Task failed.1 tables failed.
    Failed to create Table SuVikas_Core.aadhaar.aadhar_subsidy_type. Rollback sucessful 
        ERROR: syntax error at or near "`"
          Position: 150 table(s) copied.

Regards,

-Anand

Glen Conway

Not sure how the migration rules might try to munge that table name, but "SuVikas_Core.aadhaar.aadhar_subsidy_type" is 40 characters and contains ".", so one or both of those factors may be the cause of the failure.  Longer than 30 characters is only supported in Oracle 12.2 & up with compatible parameter not set to a lower version, and a valid table name must include only alphanumerics and "$", "_", and "#".

anand_gp

Hi,

This time, I tried with a smaller database name TEST.  schema name is "sample".  Table name "emp".  But still it reports same error.

When I drag and drop, it takes all these details into consideration as shown in below error message   TEST.sample.emp

Copied Objects:
pgTEST.TEST.sample.TABLE.emp
Drop Target: TEST2
Copy DDL: Yes
Replace
Copy Data: Yes
Task failed.1 tables failed.
    Failed to create Table TEST.sample.emp. Rollback sucessful 
        ERROR: syntax error at or near "`"
          Position: 150 table(s) copied.

It could be some other issue I believe.  I downloaded latest postgres JDBC jar file from postgres website.  It is of no use.  I don't know, why I am able to connect to PG database, but not able to copy table.  Thanks.

Regards,

-Anand

anand_gp

Hi,

Anybody knows how to fill this "Available source platform".  I tried with "Add Platform", but it is not working either.  In some other setup, I have seen it showing a list of connections, where the whole migration works in their platform.

Regards,

-Anand

thatJeffSmith-Oracle

it's for sql server, sybase ase, db2, teradata, mysql

there's no way to add postgresql

anand_gp

That means, even Drag & Drop doesn't work?   Is there any other way to move all tables (primarily tables at the moment.  having 100s of tables in a DB) from Postgres to Oracle?  Thanks for your clarification.

Best Regards,

-Anand

EdStevens

anand_gp wrote:That means, even Drag & Drop doesn't work? Is there any other way to move all tables (primarily tables at the moment. having 100s of tables in a DB) from Postgres to Oracle? Thanks for your clarification.Best Regards,-Anand

I'm not familiar with postgres, but I've got to believe it has some utility for unloading tables to delimited text files.  Those files could then be used by oracle sqlldr to load into oracle.  Since you have a lot of tables, you'd want to figure out some way to script the operation. 

thatJeffSmith-Oracle

No, it means the generic code we have for Drag and Drop and Copy to Oracle is busted for postgresql. We see the issue and will have a fix for 20.1