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
1.03k stars 343 forks source link

Facing issues in procedures which generated by ora2pg #1818

Open nareneee opened 1 month ago

nareneee commented 1 month ago

Hi Darold,

We are facing below error, please help us on this.

1) psql:/backend/migration/scripts/blob/procedure.sql:75: ERROR: syntax error at or near "," LINE 20: SELECT EXTRACT[kpacDraft, '//@Contact'].getClobVal()...

As i observered In Oracle, EXTRACT is used to extract parts of XML, and getClobVal() converts it to a CLOB. PostgreSQL handles XML differently. we can use xpath() to query XML data in PostgreSQL.

2) psql:/backend/migration/scripts/blob/procedure.sql:112: ERROR: syntax error at or near "table" LINE 11: select count(*) into STRICT row_count from table(xmlsequ...

In Oracle, TABLE() is used to treat the result of a subquery as a table. PostgreSQL does not have a direct equivalent, and this syntax must be adjusted depending on the context. If we are working with XML data and trying to count elements within XML, you can use xpath() to extract the elements, and array_length() to count them.

As we are building the automation tool , we are facing lot of error in procedure, function and triggers. is there any way to fix the issue by ora2pg conf level? or still do we need manual adjusments?

darold commented 1 month ago

You still need manual editing.

nareneee commented 1 month ago

Could you fix it in upcoming version?