how to migrate geometry field from postgresql(PostGIS) to sdo geometry field oracle

Question

how to convert "geom" column to sdo geometry type oracle? please help.

example postgresql data 1 row

"id","a_name_e","a_code","count","p_name_e","p_code","geom"
"1","AKAT AMNUAI","11","8","SAKON NAKHON","47","010600000001000000010300000001000000460800000622EC9016015A40E3B040576A7D31401AA5D9FE18015A40AE13C2AD427D314051532B1A18015A40BAB9E5180F7D3140FF2AC37F13015A400EB21016E47C31405C51E71A10015A40E85228B2CD7C3140A961FFC60C015A403DC7F890BF7C3140B32CB07502015A4024BD1835B47C31404A2EFD48E5005A40F83A8EB1A87C3140D4404731C7005A4005660869AA7C31401658C5ECBB005A40E44B1CB6A77C314045650660AE005A40403FBB71A07C314037105D8CA2005A400149A32A957C3140CBD653779D005A4078314D34897C3140C89A6CE299005A40A1ACA5A47E7C3140E0D733B099005A4049EC1476787C314092924CED98005A40FBF78FFF5E7C31409A3EBA619D005A40FFAC1ED6367C3140B6354D9B9E005A40E59CC5F2277C31407FDFB0D29E005A40FFFF020B1C7C3140ED5080D49D005A40570C363CF77B3140E40ADD159E005A40D61BF601F17B3140519B80349F005A40EC9118D1E77B31407137017AAD005A40FD219E6F9F7B31400460F9E5AD005A40B7B08CF79A7B3140C9D8E2AEAD005A4047DC0EDC947B31401AB55E77AD005A401298BA218D7B31409E7364C7AD005A4044AE7D8E897B314050D684ACAE005A409D95128E857B31403379297FB1005A409E0C0AC4807B3140B26ED565B2005A40FA96612C7C7B3140D739145BB2005A406D40030D767B31408584C54FB1005A403BE86BFD707B314050DFEAF2AF005A402B90BF8A6E7B3140C684D97EAF005A404DB225166A7B31401A4710ADAF005A40C05E2507607B3140361B2C3EB0005A409C70AAC35B7B31404351560CB1005A400DD4DD2D577B31407EE021B5B6005A40D44558A5417B3140744F9CACB7005A40A1603F6B3B7B3140D66E49F7B7005A40000C1FE5347B31401354B6CFB7005A404C87FEF12A7B3140981E2A25B7005A4002D06C591F7B314089817F58B4005A40D32729B0E57A3140F71A2437B4005A4034328947DF7A31401BD78083B4005A40434875B6D97A3140864EB750B5005A407DEDB635D37A314012A3F879B7005A40C4874B7DC37A31408DB583F5B7005A4016958987BF7A3140146DFD13B8005A4064AD7518B97A314028430790B8005A400C057E5A8B7A31406AD244C5B8005A40FE93A734857A31402A359F8CB9005A4064A388757B7A31403BC8056ABC005A40E537398C637A3140E9A893DEBC005A4040A671145D7A314064DEA45ABE005A402660D6FB2D7A3140E7F09280C1005A40819BD2BE017A3140EE49B312C2005A40ADD8C475F8793140A1230C00C2005A4005BBDDD6ED7931406089E73EC1005A400EACE27DBD79314099F5E65DC1005A40E0294C5AB7793140D58F2454C2005A4024A8A7D7A37931407E87CFC4C5005A40532C613C59793140FE9FBF80C8005A40F876A75E2E79314093B6A65ED0005A40FABBD930D2783140D8FCDCC8D6005A40FB58589C74783140C8B86867D7005A4075E2C2CB6C783140804A9FA6DF005A401B9D43773B7831405270B292F6005A4052054915A3773140D14C37FEF7005A4055B15905967731409D848DABF9005A40BCCB3B047C7731401E54995102015A40D5F1B014CF763140BA26FFDD02015A40CEE8B5F0BC763140CA0411D802015A4073B808C7A1763140E5070A8102015A4095FEB26D7B763140B692469402015A408FE336816E76314050D58D5204015A40DC0012F42F76314013F22818DD005A4005A3480D977631403125DD12C2005A4066A992D9C57631407B625AF4A2005A40CEFD14D9F27631408690BF4A8A005A40526372EF1D77314015B93D3063005A40C8436DA33D773140ABB3670548005A400448361D2D773140FDBB32B431005A407D3BEF30F7763140B1F9B77917005A40D2618BC8BE7631408335C0FBFDFF594057F320F8C47631402C7C2A1DDDFF5940A3E6B30CEB763140390EC1EEC1FF5940E4BE14B41077314095246F1857FF594024D1107FC4783140E55952A03BFF5940D056EC67D2783140E0712F5317FF594099DF0D79E9783140386A1E6EFDFE594043D6A9DDF278314000D53FCEDEFE594006E48106F47831406614EFA0BDFE59403E711313F378314030CF505DA0FE594052AED622E87831408CD046F773FE594003539AE4C07831404745878B5CFE5940D301428177783140B8A008AA46FE594089FE4F400F783140BB2597B121FE5940807E9E7BBB773140FC7BA9BBFDFD594039CE5356967731400BAEE0D7D7FD5940231CC8D4907731407DA27407BAFD594079509151A3773140610E9C2895FD59402223FFFBCD77314031BC482178FD59408ADBF12EE7773140D82CA6E150FD5940D8EF52BEF37731406850E69E33FD59404C036A19E577314002F810E70EFD59408E519696DC773140C1E0E76EE8FC5940979826CCE0773140DC83BA19C5F..."

Answer 1

Welcome to SO.

I'm not an Oracle expert, but I believe you could export the PostGIS geometries as WKT (Well Known Text) and then in Oracle convert them from WKT to SDO Geometry

In PostGIS you can get a WKT representation of your geometry using ST_AsText

Further reading: convert Postgres geometry format to WKT

Answer 2

PGSQL:

CREATE TABLE pg_scheme.wkt_table AS /*wkt table to get data to oracle in varchar format*/
SELECT ST_AsText(geom)::varchar as wkt
FROM  pg_scheme.geom_table

Oracle SQL:

SELECT  "wkt", SDO_UTIL.FROM_WKTGEOMETRY("wkt"), SDO_GEOMETRY("wkt",3059/*EPSG code*/)
FROM "pg_scheme"."wkt_table"@dblink_to_pg; 

Oracle does not like text format (at least where I tested) - you should convert wkt to varchar ST_AsText(geom)::varchar before getting it to Oracle.