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

REGEXP functions conversion with PG_VERSION 15 #1656

Closed ElenaLopatskaya closed 1 year ago

ElenaLopatskaya commented 1 year ago

Hi Darold,

At first, many thanks for developing such incredibly helpful migration tool!

I have a question regarding new REGEXP functions added in PostgreSQL 15 such as REGEXP_LIKE, REGEXP_SUBSTR. It means that these functions could be just moved as is from Oracle into PG 15.. But Ora2Pg still converts them via REGEXP_MATCH even if I set PG_VERSION 15 in config file.

Would it be possible to tune Ora2Pg to not apply any conversion for this syntax when migrating to PG 15?

Best regards, Elena

okbob commented 1 year ago

pá 14. 7. 2023 v 11:16 odesílatel ElenaLopatskaya @.***> napsal:

Hi Darold,

At first, many thanks for developing such incredibly helpful migration tool!

I have a question regarding new REGEXP functions added in PostgreSQL 15 such as REGEXP_LIKE, REGEXP_SUBSTR. It means that these functions could be just moved as is from Oracle into PG 15.. But Ora2Pg still converts them via REGEXP_MATCH even if I set PG_VERSION 15 in config file.

Would it be possible to tune Ora2Pg to not apply any conversion for this syntax when migrating to PG 15?

Attention - Postgres's REGEXP_LIKE and REGEXP_SUBSTR are not fully compatible with Oracle,

Best regards, Elena

— Reply to this email directly, view it on GitHub https://github.com/darold/ora2pg/issues/1656, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO42BQFAFDHPE67QJ3ULXQEE6XANCNFSM6AAAAAA2KCBA4Y . You are receiving this because you are subscribed to this thread.Message ID: @.***>

darold commented 1 year ago

Hi Elena,

@okbob is right the PostgreSQL implementation is not 100% equivalent to the Oracle function, this is why there is still the replacement with regexpmatch() even if PG15 have the same functions. If you want the exact same behavior for these regexp* functions you should use the Orafce extension and enable USE_ORAFCE in ora2pg.conf. The function will be kept untouched by Ora2Pg.