search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
1.02k stars 343 forks source link

Problem with \N value during import #1594

Closed franxav06 closed 1 year ago

franxav06 commented 1 year ago

Good morning I'm using the last version of Ora2pg on a VirtualBOX on Linux (Oracle linux 8.7) on my laptop. I'm exporting data from an Oracle database (12.1) on a Redhat server to a Postgresql 14 database on Windows. When the import program insert data into a non partitioned table, there is no problem with the null values into a column of type date (timestamp without time zone). When it try to insert data into a partition the error invalid command \N is raised The column is of type Timestamp without time zone and is of course nullable. Thanks in advance for your help

Kind regards

Francesco

darold commented 1 year ago

How about latest development code? Do you still have the issue?

franxav06 commented 1 year ago

Hello

Thanks for your quick answer. I still have the problem with the partitions. Finally I will import the tables not partitioned (after a refresh of the concerned schema) and I'll partition the tables directly in Postgres. It's nevertheless a problem that should be solved, I think….

Thanks in advance

Kind regards

Francesco

darold commented 1 year ago

I can not reproduce this issue, can you post the Oracle description of the table? Are you importing data from files using psql or are you using a direct data import with ora2pg?

franxav06 commented 1 year ago

Good morning

Thank you for your help I have the problem both using psql (on Linux and Windows) and importing directly with ora2pg. I cannot post the description of the table, it's highly confidential. What I can tell you for sure, is that I don't have the problem with not partitioned tables, I can load millions of rows with the \N value without problem, but I cannot when it's a partition of a table

Thanks in advance

Kind regards

Francesco DI TULLO

[cid:iriscare2_10030577-2013-4679-ae9b-35c6dd8d4069.png] Francesco Di Tullo

+32 2 435 64 19 Rue Belliard 71 / 2, 1040 Bruxelles www.iriscare.brusselshttp://www.iriscare.brussels [cid:linkedin_8d8965a2-43d2-493f-8eca-586d1d844175.png] https://www.linkedin.com/company/iriscare [cid:twitter_3d9b4c9a-9ac6-4dfe-b060-a680dfea7e00.png] https://twitter.com/IRISCARE_iris Pensez à l'environnement avant d'imprimer !

De : Gilles Darold @.> Envoyé : jeudi, 23 février 2023 22:58 À : darold/ora2pg @.> Cc : Francesco Di Tullo @.>; Author @.> Objet : Re: [darold/ora2pg] Problem with \N value during import (Issue #1594)

I can not reproduce this issue, can you post the Oracle description of the table? Are you importing data from files using psql or are you using a direct data import with ora2pg?

— Reply to this email directly, view it on GitHubhttps://github.com/darold/ora2pg/issues/1594#issuecomment-1442483020, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A6COOAHTUEIZGEEIH3AIRWLWY7MMZANCNFSM6AAAAAAVFMELT4. You are receiving this because you authored the thread.Message ID: @.**@.>>

franxav06 commented 1 year ago

Bonjour

J'ai trouvé le problème Une table contient un champ de type bigint NOT NULL, mais le programme essaye d'insérer un nombre décimal, cela provoque une erreur et toutes les insertions suivantes ne fonctionnent plus. Il suffit de changer le type du champ avant le chargement des données

Bien cordialement

Francesco DI TULLO

darold commented 1 year ago

Thanks for the feedback. I guess that in this case there should be an error message before all the \N error messages.

franxav06 commented 1 year ago

Hello

Thanks for your answer

Yes, in order to see it the load need to be stopped immediately after …. I'm very new into the migration from Oracle to Postgres, so it took me sometime to find the problem. Now I have to install PENTAHO_KETTLE and see which interactions are possible with ORA2PG. I have found your slides at ora2pg-performances.pdf (darold.net)https://www.darold.net/confs/ora2pg-performances.pdf

Kind regards

Francesco DI TULLO

[cid:iriscare2_10030577-2013-4679-ae9b-35c6dd8d4069.png] Francesco Di Tullo

+32 2 435 64 19 Rue Belliard 71 / 2, 1040 Bruxelles www.iriscare.brusselshttp://www.iriscare.brussels [cid:linkedin_8d8965a2-43d2-493f-8eca-586d1d844175.png] https://www.linkedin.com/company/iriscare [cid:twitter_3d9b4c9a-9ac6-4dfe-b060-a680dfea7e00.png] https://twitter.com/IRISCARE_iris Pensez à l'environnement avant d'imprimer !

De : Gilles Darold @.> Envoyé : mercredi, 1 mars 2023 10:57 À : darold/ora2pg @.> Cc : Francesco Di Tullo @.>; Author @.> Objet : Re: [darold/ora2pg] Problem with \N value during import (Issue #1594)

Thanks for the feedback. I guess that in this case there should be an error message before all the \N error messages.

— Reply to this email directly, view it on GitHubhttps://github.com/darold/ora2pg/issues/1594#issuecomment-1449743444, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A6COOABEV2PUYWUR7SMIDR3WZ4MO7ANCNFSM6AAAAAAVFMELT4. You are receiving this because you authored the thread.Message ID: @.**@.>>