issue using ora2pg conversion to postgresql for objects referred with mdsys.geomerty (specifically related to shapefiles)

[bharanisvng]

Gilles We are facing issue, using ora2pg (ver 23.2/24.0) while performing conversion to postgresql for objects referred with mdsys.geometry.

issue while accessing another schema "mdsys" geometry tables (specifically related to shapefiles)

sample command

e.g.) ora2pg -c /var/lib/ora2pg/ora2pg-XX/ora2pg.conf -s dbi:Oracle:host="XX.yy.zz.aa;sid=abc;port=1521" -u <mysuperuserid> -w 'mypassword'  -n <db_name> -t TABLE -a  <abc_bbc_tz_shapefiles>

sample error message

FATAL: _column_info() ORA-00942: table or view does not exist (DBD ERROR: error possibly near <> indicator at char 47 in 'SELECT DISTINCT c.GEOMETRY.SDO_GTYPE FROM myschema.<>abc_bbc_tz_shapefiles c WHERE ROWNUM < 50000') DBI::d=HASH(0x5618477ff950)->disconnect invalidates 2 active statement handles (either destroy statement handles or call finish on them before disconnecting) at /usr/local/share/perl5/Ora2Pg.pm line 14987. Aborting export...

we have ensured access exists for the oracle user (superuser) which we hit source schema's including mdsys schema using ora2pg tool

[darold]

If table myschema.abc_bbc_tz_shapefiles exists the failure could probably be caused by the case sensitivity. Can you try using in sqlplus connected as same user that ora2pg:

SELECT DISTINCT c.GEOMETRY.SDO_GTYPE FROM myschema.abc_bbc_tz_shapefiles c WHERE ROWNUM < 50000;

do you have the same error.

[bharanisvng]

we fixed the issue. it was due to "SELECT/Create/Insert on any table " from our specific source oracle userid, hitting "mdsys."..geometry table . pl close the ticket

[bharanisvng]

pl close the ticket