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

Oracle column aliases (after TRIM function) are syntactically incorrect due to small bug introduced in commit 66f4fc37 #1791

Closed simonpane closed 4 weeks ago

simonpane commented 4 weeks ago

Problem identified with Oracle column aliases in Ora2Pg V24.3. Problem introduced with commit 66f4fc37. PR with fix forthcoming.

TL;DR: table alias is being added to the column alias - syntactically not permitted with Oracle Database.

Full description:

Ora2Pg extract fails with errors such as:

Aborting export...16] [=======================> ] 14855954/14904276 total rows (99.7%) - (3001 sec., avg: 4950 recs/sec), REDACTED_ORACLE_TABLE_NAME in progress.
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle ORCL at /usr/local/share/perl5/Ora2Pg.pm line 15336.
Aborting export...
Aborting export...
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle ORCL at /usr/local/share/perl5/Ora2Pg.pm line 15336.

Running with debugging shows additional detail:

DEBUG: FATAL: ORA-00923: FROM keyword not found where expected (DBD ERROR: error possibly near <*> indicator at char 287 in 'SELECT ...

Manually expanding and formatting the SQL statement shows that the SQL is:

SELECT a."ID",
a."NAME",
a."DESCRIPTION",
...
trim(BOTH ' ' FROM a."COL1") AS a."COL1",
...
trim(BOTH ' ' FROM a."COLx") AS a."COLx"
FROM "REDACTED_ORACLE_SCHEMA"."REDACTED_ORACLE_TABLE_NAME" a

Problem is that the table alias (a in this case) is being added to the column alias on the TRIM column functions. The a."COL1" is not syntactically correct.

Solution is simple: remove the table alias from the column alias. PR to correct to be provided.

darold commented 4 weeks ago

Closed PR #1792