compare 2 tables with minus with a SDO Geometry shape column

Question

if I have a column like SHAPE:

CREATE TABLE SOL_PUBLIC.PUNCTE_PERIMETRU_SIT
    (
      ID                NUMBER(10)                  NOT NULL,
      NUMAR             NUMBER(19),
      X                 NUMBER(19,2),
      Y                 NUMBER(19,2),
      FK_ID_CHESTIONAR  NUMBER(19),
      SHAPE
    )
    COLUMN SHAPE NOT SUBSTITUTABLE AT ALL LEVELS
    VARRAY "SHAPE"."SDO_ELEM_INFO" STORE AS LOB (
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      CACHE
      LOGGING
          STORAGE    (
                      INITIAL          64K
                      NEXT             1M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    VARRAY "SHAPE"."SDO_ORDINATES" STORE AS LOB (
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      CACHE
      LOGGING
          STORAGE    (
                      INITIAL          64K
                      NEXT             1M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))

when I try:

select * from (
select pub.nume_sit,cpu.* from
    SOL_PUBLIC.PUNCTE_PERIMETRU_SIT@RACDB cpu
    inner join SOL_PUBLIC.SOL_CHESTIONARE@RACDB PUB
        on cpu.FK_ID_CHESTIONAR=PUB.ID
minus
select prod.nume_sit,cpr.numar,cpr.x,cpr.y from 
    SOL.PUNCTE_PERIMETRU_SIT cpr
    inner join SOL.SOL_CHESTIONARE PROD
        on cpr.FK_ID_CHESTIONAR=PROD.ID  
) tmp_minus 
ORDER BY nume_sit,numar;

it gives me an:

ORA-22804: remote operations not permitted on object tables or user-defined type columns

and if I make a:

select * from
    SOL_PUBLIC.PUNCTE_PERIMETRU_SIT@RACDB cpu
minus
select * from 
    SOL.PUNCTE_PERIMETRU_SIT cpr

it gives me an:

ORA-22901: cannot compare VARRAY or LOB attributes of an object type

I need to insert the missing columns in the table where they are missing (so I need the SHAPE too).

I think I have to convert iti tu text to compare it and when I insert it into the table have to convert it back. How can I do it?

MANY THANKS!

Answer 1

I found that the only way to compare with RACDB + SDO_GEOMETRY row is to compose the SDO like this:

select tm.id,tm.nume_sit,tm.numar,tm.x,tm.y,
        MDSYS.SDO_GEOMETRY(2001,NULL,
                           MDSYS.SDO_POINT_TYPE(tm.x,tm.y,NULL),
                           NULL, NULL) as shape
from (
select pub.id,pub.nume_sit,cpu.numar,cpu.x,cpu.y from 
    SOL_PUBLIC.PUNCTE_PERIMETRU_SIT@RACDB cpu
    inner join SOL_PUBLIC.SOL_CHESTIONARE@RACDB PUB
        on cpu.FK_ID_CHESTIONAR=PUB.ID
minus
select prod.id,prod.nume_sit,cpr.numar,cpr.x,cpr.y from 
    SOL.PUNCTE_PERIMETRU_SIT cpr
    inner join SOL.SOL_CHESTIONARE PROD
        on cpr.FK_ID_CHESTIONAR=PROD.ID  
) tm
ORDER BY nume_sit,numar;

as for converting the SDO_GEOMETRY to text, if anyone need this, you have to do something like this:

to_char(substr(cpu.shape.get_wkt(),1,4000)) as shape