RE: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

-----Original Message-----
From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Sent: Thursday, August 20, 2020 10:42 AM
To: Ko, Christina (US) <christina(dot)ko(at)lmco(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org
Cc: Ho, Chuong <chuongho(at)amazon(dot)com>
Subject: EXTERNAL: Re: Loading Oracle Spatial Data to Postgresql

On 8/20/20 7:30 AM, Ko, Christina wrote:
> Hi All,
>
> I am running into issue using DMS to load oracle spatial data (oracle
> 11.2.0.3) to postgresql.
>
> Oracle table:
>
> CREATETABLESpatial_Tbl
>
> IDNUMBER(38,9),
>
>   P_ID NUMBER(38,9),
>
> GEOMETRY  MDSYS.SDO_GEOMETRY
>
> AWS DMS calls the SDO2GEOJSON custom function trying to load the data,
> but it failed and the Geometry columns in Postgresql was empty

The query?

The error messages?
CK - Error insert null to column, looks like DMS is having problem converting Spatial data.

Postgres version?

Postgres table schema?
CK -
CREATE TABLE modstar.p_mstdbgeospatial
(
id numeric(38,9) NOT NULL,
p_id numeric(38,9),
geometry geometry,
CONSTRAINT p_mstdbgeospatial_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)

>
> I also tried using SDO_UTIL.TO_GEOJSON utility and didn't work either.
>
> I am looking for suggestions of how to load the geometry data from
> oracle 11.2.0.3 to oracle.

I'm assuming you mean oracle 11.2.0.3 to Postgres?
CK - Correct. oracle 11.2.0.3 to Postgres
Thank you for your response Adrian.

>
> Thanks in advanced for your help!
>
> Christina
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com