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.03k stars 343 forks source link

Table columns are missing in COPY export #1370

Closed manousek closed 2 years ago

manousek commented 2 years ago

Hi,

I have successfully migrated in the past some our databases from Oracle to Postgres using excellent ora2pg. I'm in trouble now with next migration.

ora2pg generator (COPY) omits some table columns in version v23.0.

I have

The resulting sql ora2pg -t COPY v23.0:
_COPY vznikpredmetu (id,sbirkovypredmet_id,misto,poznamka,uins,uupd,lokalita_id,poznamkaautor,specifikacemista_id,roleautora_id,autor_id,datace_id,mig_vznikpredmetu_puv_id,mig_autorpredmetu_id,mig_datacepredmetuid) FROM STDIN; v21.0:
_COPY vznikpredmetu (id,sbirkovypredmet_id,misto,poznamka,uins,uupd,dins,dupd,lokalita_id,poradi,snadautor,poznamkaautor,specifikacemista_id,roleautora_id,autor_id,datace_id,mig_vznikpredmetu_puv_id,mig_autorpredmetu_id,mig_datacepredmetuid,snadlokalita,novum) FROM STDIN;

Columns are missing from v23.0 export: dins,dupd,poradi,snadautor,snadlokalita,novum.

I tryed the same config file from v.21.0 in v23.0 but the result was the same - missing columns.

Please, is this known problem or I am doing something wrong?

Best regards, Jaroslav

manousek commented 2 years ago

supplement: all missing columns have defined DATA_DEFAULT in oracle db.

darold commented 2 years ago

This llok to be a regression, can you please post the Oracle DDL of the table creation so that I can try to reproduce?

manousek commented 2 years ago

Hi,

I tryed these version with the same table: ora2pg-21.0 ok ora2pg-22.1 ok ora2pg-23.0 not ok

Oracle is Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

DDL generated from sqldeveloper:

CREATE TABLE VZNIKPREDMETU ( ID NUMBER(12,0) NOT NULL ENABLE, SBIRKOVYPREDMET_ID NUMBER(12,0), MISTO VARCHAR2(300 CHAR), POZNAMKA VARCHAR2(2000 CHAR), UINS VARCHAR2(100 CHAR), UUPD VARCHAR2(100 CHAR), DINS DATE DEFAULT SYSDATE, DUPD DATE DEFAULT SYSDATE, LOKALITA_ID NUMBER(12,0), PORADI NUMBER(12,0) DEFAULT 1 NOT NULL ENABLE, SNADAUTOR NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, POZNAMKAAUTOR VARCHAR2(2000 BYTE), SPECIFIKACEMISTA_ID NUMBER(12,0), ROLEAUTORA_ID NUMBER(12,0), AUTOR_ID NUMBER(12,0), DATACE_ID NUMBER(12,0) NOT NULL ENABLE, MIG_VZNIKPREDMETU_PUV_ID NUMBER(12,0), MIG_AUTORPREDMETU_ID NUMBER(12,0), MIG_DATACEPREDMETU_ID NUMBER(12,0), SNADLOKALITA NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, NOVUM NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, CONSTRAINT VZNIKDATACE_FK FOREIGN KEY (DATACE_ID) REFERENCES DATACE (ID) ENABLE, CONSTRAINT VZNIKSUBJEKT_FK FOREIGN KEY (AUTOR_ID) REFERENCES SUBJEKT (ID) ENABLE, CONSTRAINT VZNIKROLE_FK FOREIGN KEY (ROLEAUTORA_ID) REFERENCES ROLE (ID) ENABLE, CONSTRAINT VZNIKSPECVZTAHU_FK FOREIGN KEY (SPECIFIKACEMISTA_ID) REFERENCES SPECIFIKACEVZTAHU (ID) ENABLE, CONSTRAINT VZNIKSP_FK FOREIGN KEY (SBIRKOVYPREDMET_ID) REFERENCES PREDMET (ID) ENABLE, CONSTRAINT VZNIKPREDMETULOKALITAFK FOREIGN KEY (LOKALITA_ID) REFERENCES LOKALITA (ID) ENABLE )

ALTER TABLE VZNIKPREDMETU ADD CONSTRAINT VZNIKPREDMETU_PK PRIMARY KEY (ID) USING INDEX VZNIKPREDMETU_PK ENABLE; CREATE INDEX XVZNIKPREDMETULOKALITA ON VZNIKPREDMETU (LOKALITA_ID) CREATE INDEX XVZNIKPREDMETU_AK ON VZNIKPREDMETU (SBIRKOVYPREDMET_ID, PORADI) CREATE INDEX XVZNIKROLE ON VZNIKPREDMETU (ROLEAUTORA_ID) CREATE INDEX XVZNIKSPECVZTAHU ON VZNIKPREDMETU (SPECIFIKACEMISTA_ID) CREATE INDEX XVZNIKSUBJEKT ON VZNIKPREDMETU (AUTOR_ID)

darold commented 2 years ago

I'm not able to reproduce the issue with latest development code, please give it a try and confirm if you still have the issue.

darold commented 2 years ago

Also take care that you have not redefined the table in MODIFY_STRUCT or use a dedicated query to extract the data with REPLACE_QUERY in ora2pg.conf

manousek commented 2 years ago

Hi darold, thank you. First, I never used MODIFY_STRUCT or REPLACE_QUERY.

Today, I made on server new installation of v23.0. (old is deleted). I used the SAME conf file like before. And.... export VZNIKPREDMETU is ok. I created extra nex simple table and tryed exports with v23 and v22.1 Both are ok now.

So... I don't understand it. There is no change on Oracle DB server. There is the same conf file. Only two changes are there: new installation of v22.1 (other folder, same perl) and new installation v.23.

Thank you very much for your time and sorry for not confirmed issue.

In next weeks I will make next migrations. I will watch the data and if a problem occurs, I'll write.

Best regards, Jaroslav

CREATE TABLE TEST ( ID NUMBER(12,0) NOT NULL ENABLE, NORMAL_FIELD VARCHAR2(300 CHAR), DEFAULT_FIELD NUMBER(12,0) DEFAULT 1 NOT NULL ENABLE );

insert into TEST values (1, 'one', 1); insert into TEST values (2, 'two', 2); insert into TEST values (3, 'three', 3);

ALLOW TEST DROP_FKEY 1 DROP_INDEXES 0 DISABLE_TRIGGERS USER TRUNCATE_TABLE 1

v22.1 COPY test (id,normal_field,default_field) FROM STDIN; 1 one 1 2 two 2 3 three 3 .

v23.0 COPY test (id,normal_field,default_field) FROM STDIN; 1 one 1 2 two 2 3 three 3 .