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
978 stars 341 forks source link

Ora2pg ignores Trigger that is disable in Oracle due to EXPORT_INVALID. #1750

Closed dcgadmin closed 3 months ago

dcgadmin commented 3 months ago

In Ora2pg EXPORT_INVALID is by default disable and only export or convert objects that is VALID in Oracle.

For Trigger, we have following conditions in the code, that fetch Trigger that is only Enable. Hence it is missing exporting Trigger that is disable but not invalid. Ideally the status of invalid should be driven by dba_objects view.

my $str = "SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_NAME, TRIGGER_BODY, WHEN_CLAUSE, DESCRIPTION, ACTION_TYPE, OWNER FROM $self->{prefix}_TRIGGERS WHERE 1=1";
    if (!$self->{export_invalid}) {
        $str .= " AND STATUS='ENABLED'";
    } elsif ($self->{export_invalid} == 2) {
        $str .= " AND STATUS <> 'ENABLED'";
    }

As per Documentation, EXPORT_INVALID types applies to 

The 'VALID' or
 'INVALID' status applies to functions, procedures, packages and userdefined types.

AFAIK, we can ignore EXPORT_INVALID for checking only Enable if not, Document need to be updated accordingly. IF needed can submit a patch to merge.

darold commented 3 months ago

Yes please submit a patch.

darold commented 3 months ago

Commit a29a4b5 fixes the documentation but I'm seriously thinking of changing that for INVALID object too, not to disabled triggers.

darold commented 3 months ago

Commit 37d6dc9 makes triggers export acts like others objects following the EXPORT_INVALID value. The ENABLE/DISABLE status is not more taken in account.