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

COPY and REPLACE_QUERY with XMLTYPE don,t work #1681

Closed deepadrenalin closed 4 months ago

deepadrenalin commented 10 months ago

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 Ora2Pg v23.2

I use the REPLACE_QUERY parameter in the config file to migrate the data of a table to postgres using the COPY function: "REPLACE_QUERY ar_artifact[SELECT * FROM ar_artifact where sourceid = 'ATN000001DUA' and sequencenumber=1 and artifacttype='OM_ORDER']" This works fine for all data types (BLOB, number, varchar, etc.) except the XMLTYPE. All XMLTYPE have "()" as content. The behaviour is the same for both direct export to Postgres and via output file. An error is not logged. But if I use the WHERE parameter, the XML type is exported correctly: "WHERE ar_artifact[sourceid = 'ATN000001DUA' and sequencenumber=1 and artifacttype='OM_ORDER']" Maybe there is a small bug in ora2pg? Joerg, Berlin-Germany

darold commented 4 months ago

This is not a bug, if you decide to provide your own query you must be aware that XMLTYPE must be exported using the getClobVal() function. So you must use it in your SELECT statement en also specify all columns names in the target list.