Migration Oracle spatial data error

[ruralqiu]

FATAL: _column_info() ORA-00942: table or view does not exist (DBD ERROR: error possibly near <> indicator at char 49 in 'SELECT sdo_cs.map_oracle_srid_to_epsg(SRID) FROM <>DBA_SDO_GEOM_METADATA WHERE TABLE_NAME=:p1 AND COLUMN_NAME=:p2 AND OWNER=:p3') DBI::db=HASH(0x24ea320)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /usr/local/share/perl5/Ora2Pg.pm line 15485. Aborting export...

[ruralqiu]

oracle:

---

-- Table structure for T_GIS_KJZBDZB_COPY

---

DROP TABLE "ORAGIS"."T_GIS_KJZBDZB_COPY"; CREATE TABLE "ORAGIS"."T_GIS_KJZBDZB_COPY" ( "ID" VARCHAR2(32 BYTE) NOT NULL, "ZYID" VARCHAR2(50 BYTE) NOT NULL, "ZYLX" VARCHAR2(12 BYTE) NOT NULL, "SHAPE" "MDSYS"."SDO_GEOMETRY" NOT NULL, "IS_EFFECTIVE" CHAR(1 BYTE) DEFAULT 1 NOT NULL, "CREATE_TIME" TIMESTAMP(6) NOT NULL, "UPDATE_TIME" TIMESTAMP(6) NOT NULL, "ORG_CODE" VARCHAR2(50 BYTE) NOT NULL, "CREATE_USER" VARCHAR2(50 BYTE) NOT NULL ) LOGGING NOCOMPRESS PCTFREE 10 INITRANS 1 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT ) PARALLEL 1 NOCACHE DISABLE ROW MOVEMENT ;

data: INSERT INTO "ORAGIS"."T_GIS_KJZBDZB_COPY" VALUES ('760c4b12d39d40179ee3ad898a9c644b', 'be71a21c62194004852070f33e60b968', 'SGXX', "MDSYS"."SDO_GEOMETRY"(2001,8307,"MDSYS"."SDO_POINT_TYPE"(83.419189453125,42.890625,98.11254487),NULL,NULL), '1', TO_TIMESTAMP('2018-03-28 11:59:59.362000', 'SYYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP('2018-03-28 11:59:59.362000', 'SYYYY-MM-DD HH24:MI:SS:FF6'), '65010200000000000001', 'fc9d67c9f03f44f6a63886015485dc83');

[Pierre3939]

Hi, I have the same error with "SHAPE" "MDSYS"."SDO_GEOMETRY"

FATAL: _column_info() ORA-00942: table or view does not exist (DBD ERROR: error possibly near <> indicator at char 49 in 'SELECT sdo_cs.map_oracle_srid_to_epsg(SRID) FROM <>DBA_SDO_GEOM_METADATA WHERE TABLE_NAME=:p1 AND COLUMN_NAME=:p2 AND OWNER=:p3')

ora2pg-24.2

[darold]

Commit ea1103c fixes this issue.

[ruralqiu]

Okay, thank you. I saw it, but there was an error while migrating Oracle spatial data. Could you please investigate and resolve the issue? Thank you again！

[darold]

Sorry I have missed the data import error. Commit b791246 fixes this problem.

[ruralqiu]

Hello, I have updated the latest code, but the same error still persists. Please investigate and handle it again. Thank you again

------------------ 原始邮件 ------------------ 发件人: "darold/ora2pg" @.>; 发送时间: 2024年3月25日(星期一) 中午11:35 @.>; @.**@.>; 主题: Re: [darold/ora2pg] Migration Oracle spatial data error (Issue #1758)

Sorry I have missed the data import error. Commit b791246 fixes this problem.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: @.***>

[Pierre3939]

Hi, I updated the code but I still get the same error.

FATAL: _column_info() ORA-00942: table or view does not exist (DBD ERROR: error possibly near <> indicator at char 49 in 'SELECT sdo_cs.map_oracle_srid_to_epsg (SRID) FROM <>DBA_SDO_GEOM_METADATA WHERE TABLE_NAME=:p1 AND COLUMN_NAME=:p2 AN D OWNER=:p3')

[darold]

How are you updating the code? You can use git clone or git pull if it is already done or use the download approach:

wget https://github.com/darold/ora2pg/archive/refs/heads/master.zip
unzip master.zip
cd ora2pg-master
perl Makefile.PL
make
sudo make install

[darold]

All references to the geometrics metadata are hardcoded to look in ALL_SDO_GEOM_METADATA in latest development code so a call to DBA_SDO_GEOM_METADATA mean that you are using an older code.

[Pierre3939]

Thanks, it worked :)

[Pierre3939]

Now I have an error when importing postgresql with import_all.sh I use PostgreSQL 14

ALTER TABLE psql:./schema/tables/table.sql:425: ERROR: type "geometry" does not exist LINE 58: shape geometry(GEOMETRY,4326), ^ ERROR: an error occurs when importing file ./schema/tables/table.sql.

[Pierre3939]

sorry I didn't have posgis installed. it works. :)