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

TRANFORM_VALUE produces invalid SQL #1766

Closed cchet closed 2 months ago

cchet commented 3 months ago

When we use the directive 'TRANSFORM_VALUE' then we et an error because of an invalid SQL. We tried with : and , to separate the column from the actual SQL, and both produce the same error.

TRANSFORM_VALUE TBL_OFFER_ENTITY[OFFER,regexp_replace("OFFER",'\u0000', '')] TRANSFORM_VALUE TBL_OFFER_ENTITY[OFFER:regexp_replace("OFFER",'\u0000', '')]

The error we get:

FATAL: ORA-00904: "A"."REGEXP_REPLACE": invalid identifier (DBD ERROR: error possibly near <> indicator at char 83 in 'SELECT a."UUID",a."CAR_LIST_ID",a."UCID",a."EXTERNAL_ID",a."BRAND_CODE",a."SOURCE",<\>a.regexp_replace("OFFER",'\u0000', ''),a."OFFERNUMBER",a."CREATEDAT",a."OFFERSTATUS",a."MARKET",a."BRAND_DESIGNATION",a."CARHUB_UUID" FROM "SOMAT"."TBL_OFFER_ENTITY" a')

ora2pg docker image: 24.3

darold commented 2 months ago

Commit b80d7aa fixes this issue. Just remember to prefix the column name used with the a alias used by ora2pg if necessary.

cchet commented 2 months ago

@darold Thank's for fixing this issue. When will be the next release, including the docker image, containing this fix?