Re: Loading Oracle Spatial Data to Postgresql

Gilles Darold <gilles@xxxxxxxxxx> · Thu, 20 Aug 2020 16:54:18 +0200



    Le 20/08/2020 à 16:30, Ko, Christina a
      écrit :

    
        Hi All,
         
        I am running into issue using DMS to load
          oracle spatial data (oracle 11.2.0.3) to postgresql.
         
        Oracle table:
         
        CREATE
          TABLE
            Spatial_Tbl
         ID       
          NUMBER(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
        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.
         
        Thanks in advanced for your help!
         
        Christina
        

    Hi,
    

    You can either use oracle_fdw or Ora2Pg. The first is a FDW
      extension that will allow you to use a foreign table to upload the
      data into your destination table and the second tool export data
      from the Oracle database to plain text file or to your PostgreSQL
      table directly.

    
    Best regards,

    
    -- 
Gilles Darold
http://www.darold.net/