Hi,
I have an original PostgreSQL table defined like this:
---
Table "public.airports_wkt"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+---------
airport_id | integer | | |
name | character varying(255) | | |
city | character varying(75) | | |
country | character varying(75) | | |
iata_faa | character varying(20) | | |
icao | character varying(20) | | |
latitude | double precision | | |
longitude | double precision | | |
altitude | numeric(12,6) | | |
timezone | character varying(100) | | |
dst | character varying(20) | | |
tz | character varying(255) | | |
srid | integer | | |
geog_ewkt | text | | |
---
When accessed through Oracle the datatype "text" is translated to "long":
SQL> desc "view_airports"@PG
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
airport_id NUMBER(10)
name VARCHAR2(765)
city VARCHAR2(765)
country VARCHAR2(765)
iata_faa VARCHAR2(360)
icao VARCHAR2(360)
latitude FLOAT(126)
longitude FLOAT(126)
altitude NUMBER(12,6)
timezone VARCHAR2(765)
dst VARCHAR2(360)
tz VARCHAR2(765)
srid NUMBER(10)
geog_ewkt LONG
But long values are difficult to manipulate...
create table airports_wkt
as select to_lob("geog_ewkt") as aa from "airports_wkt"@PG;
---
ERROR at line 2:
ORA-00997: illegal use of LONG datatype
---
or:
select to_lob("geog_ewkt") as aa from "airports_wkt"@PGNUCLEAR;
---
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
---
Does someone know how to handle this situation?
Thanks and Regards