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
991 stars 342 forks source link

STOP_ON_ERROR is ignored for direct data copy indexes re-creation #1646

Closed pavel-moskotin-db closed 11 months ago

pavel-moskotin-db commented 1 year ago

For ora2pg:23.2 in the following scenario structures and data are being migrated successfully:

STOP_ON_ERROR 0 LOG_ON_ERROR 1 DROP_INDEXES 1

However indexes re-creation interrupts in case of unsupported function: [2023-06-17 08:29:30] Restoring foreign keys of table ... [2023-06-17 08:29:33] Restoring indexes of table ... DBD::Pg::db do failed: ERROR: function trunc(timestamp without time zone) does not exist LINE 1: CREATE INDEX ON ((trunc(cast(sn... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. at /usr/local/share/perl5/Ora2Pg.pm line 8702. [2023-06-17 08:32:51] FATAL: ERROR: function trunc(timestamp without time zone) does not exist LINE 1: CREATE INDEX ON ((trunc(cast(sn... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts., SQL: CREATE INDEX ON ((trunc(cast( as timestamp)))); Aborting export...

Thus multiple "good" indexes are not being created after first one fails.

Is it possible to add native support for Oracle:trunc => PG:date_trunc function? If not, is it possible to honour "STOP_ON_ERROR 0" while re-creating the indexes?

darold commented 11 months ago

Normally now Ora2Pg always convert Oracle trunc() function to date_trunc(), alternatively you can also use orafce that adds the trunc() function for dates.