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

ora2pg WHERE condition or REPLACE_QUERY not working #1664

Closed stephb-pixel closed 1 year ago

stephb-pixel commented 1 year ago

Hi all,

I have this following issue when using ora2pg with a WHERE condition or REPLACE_QUERY : it's not exporting any rows.

Here is an example:

In the config file:

WHERE 1=1 # Apply to all tables

WHERE TABLE_TEST[ID1='001'] # Apply only on table TABLE_TEST

WHERE TABLE_TEST[ID1='001' OR ID1='002] DATE_CREATE > '2001-01-01' TABLE_INFO[NAME='test']

WHERE CREATIE_DATUM < '01-JAN-09'

In the debug log:

[2023-08-11 16:33:54] Retrieving partitions information... [2023-08-11 16:33:55] Dumping data from CONSTRUCTIE to file: tmp_CONSTRUCTIE_data.sql [2023-08-11 16:33:55] Dumping data from CONSTRUCTIE to file: tmp_CONSTRUCTIE_data.sql [2023-08-11 16:33:55] Looking how to retrieve data from CONSTRUCTIE... [2023-08-11 16:33:55] Applying WHERE global clause: CREATIE_DATUM < '01-JAN-09' [2023-08-11 16:33:55] DEGUG: Query sent to Oracle: SELECT "ID","VERSIENUMMER", < all columns > FROM "EPB_OWNER"."CONSTRUCTIE" a WHERE (CREATIE_DATUM < '01-JAN-09') [2023-08-11 16:33:55] Fetching all data from CONSTRUCTIE tuples... [2023-08-11 16:34:09] DEBUG: number of rows 0 extracted from table CONSTRUCTIE [2023-08-11 16:34:09] DEBUG: Creating output for 10000 tuples [2023-08-11 16:34:09] Dumping data from CONSTRUCTIE to file: tmp_CONSTRUCTIE_data.sql [2023-08-11 16:34:09] Extracted records from table CONSTRUCTIE: total_records = 0 (avg: 0 recs/sec)

[> ] 0/31715870 total rows (0.0%) - (14 sec., avg: 0 recs/sec). [2023-08-11 16:34:09] Renaming temporary file ./data/tmp_CONSTRUCTIE_data.sql into ./data/CONSTRUCTIE_data.sql [2023-08-11 16:34:09] Total time to export data from 1 tables (0 partitions, 0 sub-partitions) and 31715870 total rows: 15 wallclock secs ( 0.04 usr + 0.02 sys = 0.06 CPU) [2023-08-11 16:34:09] Speed average: 2114391.33 rows/sec

When reversing the condition to : WHERE CREATIE_DATUM > '01-JAN-09' , it would retrieve all rows instead, thus not applying the filter either.

And not specifying any condition would retrieve the data as it should , thus there is no connection issue to the DB. We need to split the data by date because of the size.

What could be the reason ?

Thank you

darold commented 1 year ago

The only reason I see is that you have no record bellow '01-JAN-09'.

stephb-pixel commented 1 year ago

Thank you for your reply. There are in fact well records below that date. I have been trying with other dates as well.

SQL> select count(*) from CONSTRUCTIE WHERE CREATIE_DATUM < '01-JAN-09';

COUNT(*)

214305

SQL> select count(*) FROM "EPB_OWNER"."CONSTRUCTIE" WHERE (CREATIE_DATUM < '01-JAN-09');

COUNT(*)

214305
darold commented 1 year ago

How about if you use the same query reported at DEGUG: Query sent to Oracle: ..., what is the count(*) reported?

stephb-pixel commented 1 year ago

It's retrieving the rows when that query is tried from sqlplus

[2023-08-14 09:23:18] Isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED [2023-08-14 09:23:18] Retrieving partitions information... [2023-08-14 09:23:23] Dumping data from CONSTRUCTIE to file: tmp_CONSTRUCTIE_data.sql [2023-08-14 09:23:23] Dumping data from CONSTRUCTIE to file: tmp_CONSTRUCTIE_data.sql [2023-08-14 09:23:23] Looking how to retrieve data from CONSTRUCTIE... [2023-08-14 09:23:23] Applying WHERE global clause: CREATIE_DATUM < '01-JAN-09' [2023-08-14 09:23:23] DEGUG: Query sent to Oracle: SELECT "ID","VERSIENUMMER","AG_ID","SOORT","TYPE","NAAM","ENERGIESECTOR","HELLING","OPPERVLAKTE","R_WAARDE","U_WAARDE","BU_WAARDE","VRIJGESTELD_VAN_BOETE","BOETE","SCHILDEEL_NAAM","SCHILDEEL_HELLING","AR_CODE","AR_BEGRENZING","VG_U_WAARDE","VG_BU_WAARDE","VG_AANTAL","VG_HT_WAARDE","VG_LUIKTYPE","VG_LUIKTYPE2","CREATIE_DATUM","CREATIE_GEBRUIKER","WIJZIGING_DATUM","WIJZIGING_GEBRUIKER","TECHNISCHE_STATUS","AR_AARD","AR_BESTEMMING","GD_ID","BESTAAND","U_EIS","R_EIS","VG_UG_EIS","ORIENTATIE","VG_SURFACE_GLAS","FREE_SURFACE_BOETE","NON_FREE_SURFACE_BOETE","VENTILATIEZONENAAM","VG_ZONNEWINST_G_GLAS","VG_LUIKTYPE_NIET_IN_VLAK","VG_VERT_HOEK_NIET_IN_VLAK","VG_LINK_HOEK_NIET_IN_VLAK","VG_RECHT_HOEK_NIET_IN_VLAK","VG_BESCHADUWING_METHODE","VG_HOEKEN_HORIZON","VG_HOEKEN_LINKER","VG_HOEKEN_RECHTER","VG_HOEKEN_VERT","VG_ORIENTATIE","FINE_R","FINE","VG_NON_FREE_SURFACE_GLAS","VG_FREE_SURFACE_GLAS","SV_ID","MUUR_DIKTE","MUUR_ING_DIEPTE","MUUR_R","VLOER_PERIMETER","VLOER_HOOG_BOV_MAAIVELD","VLOER_R","VLOER_OPPERVLAKTE","VLOER_RAND_ISOLATIE","RAND_ISO_LAMBDA_H","RAND_ISO_BREEDTE_H","RAND_ISO_DIKTE_H","RAND_ISO_LAMBDA_V","RAND_ISO_HOOGTE_V","RAND_ISO_DIKTE_V","VLOER_ING_DIEPTE","BEREKENINGSMETHODE","BEREKENINGSMETHODEGG","VG_VAST_KADER","VG_INBRAAKRISICO","VG_ENKEL_KIPSTAND_OPP","VG_DRAAIKIP_OF_DRAAISTAND_OPP","VG_DRAAIKIP_OF_KIPSTAND_OPP","NA_GEISOLEERD","VG_TYPE_BEGLAZING","VG_TYPE_PROFIEL","VG_U_WAARDE_PROFIEL" FROM "EPB_OWNER"."CONSTRUCTIE" a WHERE (CREATIE_DATUM < '01-JAN-09') [2023-08-14 09:23:23] Fetching all data from CONSTRUCTIE tuples... [2023-08-14 09:23:33] DEBUG: number of rows 0 extracted from table CONSTRUCTIE [2023-08-14 09:23:33] DEBUG: Creating output for 10000 tuples [2023-08-14 09:23:33] Dumping data from CONSTRUCTIE to file: tmp_CONSTRUCTIE_data.sql [2023-08-14 09:23:33] Extracted records from table CONSTRUCTIE: total_records = 0 (avg: 0 recs/sec)

[> ] 0/31715870 total rows (0.0%) - (10 sec., avg: 0 recs/sec). [2023-08-14 09:23:33] Renaming temporary file ./data/tmp_CONSTRUCTIE_data.sql into ./data/CONSTRUCTIE_data.sql [2023-08-14 09:23:33] Total time to export data from 1 tables (0 partitions, 0 sub-partitions) and 31715870 total rows: 15 wallclock secs ( 0.04 usr + 0.01 sys = 0.05 CPU) [2023-08-14 09:23:33] Speed average: 2114391.33 rows/sec

SQL> SELECT "ID","VERSIENUMMER","AG_ID","SOORT","TYPE","NAAM","ENERGIESECTOR","HELLING","OPPERVLAKTE","R_WAARDE","U_WAARDE","BU_WAARDE","VRIJGESTELD_VAN_BOETE","BOETE","SCHILDEEL_NAAM","SCHILDEEL_HELLING","AR_CODE","AR_BEGRENZING","VG_U_WAARDE","VG_BU_WAARDE","VG_AANTAL","VG_HT_WAARDE","VG_LUIKTYPE","VG_LUIKTYPE2","CREATIE_DATUM","CREATIE_GEBRUIKER","WIJZIGING_DATUM","WIJZIGING_GEBRUIKER","TECHNISCHE_STATUS","AR_AARD","AR_BESTEMMING","GD_ID","BESTAAND","U_EIS","R_EIS","VG_UG_EIS","ORIENTATIE","VG_SURFACE_GLAS","FREE_SURFACE_BOETE","NON_FREE_SURFACE_BOETE","VENTILATIEZONENAAM","VG_ZONNEWINST_G_GLAS","VG_LUIKTYPE_NIET_IN_VLAK","VG_VERT_HOEK_NIET_IN_VLAK","VG_LINK_HOEK_NIET_IN_VLAK","VG_RECHT_HOEK_NIET_IN_VLAK","VG_BESCHADUWING_METHODE","VG_HOEKEN_HORIZON","VG_HOEKEN_LINKER","VG_HOEKEN_RECHTER","VG_HOEKEN_VERT","VG_ORIENTATIE","FINE_R","FINE","VG_NON_FREE_SURFACE_GLAS","VG_FREE_SURFACE_GLAS","SV_ID","MUUR_DIKTE","MUUR_ING_DIEPTE","MUUR_R","VLOER_PERIMETER","VLOER_HOOG_BOV_MAAIVELD","VLOER_R","VLOER_OPPERVLAKTE","VLOER_RAND_ISOLATIE","RAND_ISO_LAMBDA_H","RAND_ISO_BREEDTE_H","RAND_ISO_DIKTE_H","RAND_ISO_LAMBDA_V","RAND_ISO_HOOGTE_V","RAND_ISO_DIKTE_V","VLOER_ING_DIEPTE","BEREKENINGSMETHODE","BEREKENINGSMETHODEGG","VG_VAST_KADER","VG_INBRAAKRISICO","VG_ENKEL_KIPSTAND_OPP","VG_DRAAIKIP_OF_DRAAISTAND_OPP","VG_DRAAIKIP_OF_KIPSTAND_OPP","NA_GEISOLEERD","VG_TYPE_BEGLAZING","VG_TYPE_PROFIEL","VG_U_WAARDE_PROFIEL" FROM "EPB_OWNER"."CONSTRUCTIE" a WHERE (CREATIE_DATUM < '01-JAN-09');

...

214305 rows selected.

SQL>

darold commented 1 year ago

Then your data should be in ./data/CONSTRUCTIE_data.sql if there is no data I don't know what happen, it might depend of the Oracle connection/schema but I don't think it is an Ora2Pg problem.