Home » Server Options » Spatial » ORA-22804 (11.2.0.2.0 - 64bit)
ORA-22804 [message #638835] Mon, 22 June 2015 15:12 Go to next message
sa_dwhite
Messages: 15
Registered: August 2013
Location: United States
Junior Member
I have 2 identical tables in 2 databases, our process does all of our ETL processing in a "prep" database then does bulk inserts updates and deletes across a link to the "live database". The below process raises the ORA-22804: remote operations not permitted on object tables or user-defined type columns. I have tried putting the procedure on both sides of the link to no avail. is there any work around for this?


SQL> desc core_vow_geo_sale
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROPERTY_ID                               NOT NULL NUMBER
 ADDRESS_GEOMETRY                                   MDSYS.SDO_GEOMETRY


PROCEDURE iCVPGeoSale(isrc  IN number)
IS
BEGIN
    INSERT INTO core_vow_geo_sale@sagwadb
    (property_id, address_geometry)
    SELECT property_id, address_geometry
      FROM core_vow_geo_sale A,
           core_vow_prop_status b
     WHERE property_id = b.prop_id
       AND b.prop_source = isrc
       AND b.prop_update = 2;
EXCEPTION
WHEN OTHERS THEN
    RAISE;
END;     

Re: ORA-22804 [message #638837 is a reply to message #638835] Mon, 22 June 2015 15:21 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ADDRESS_GEOMETRY sounds as if it is a coordinate that contains X and Y values. If that's correct, transfer those (X and Y) values and recreate the SDO_GEOMETRY value. You'll do that either into another table and then UPDATE the target table, or into the CORE_VOEW_GEO_SALE (you might need to alter it in order to add X and Y) and use AFTER INSERT database trigger which will calculate SDO_GEOMETRY value.
Re: ORA-22804 [message #638838 is a reply to message #638835] Mon, 22 June 2015 15:28 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
This may sound a little roundabout. Supposedly you can create table as select from a remote database using a dblink, where the table contains spatial data. So, theoretically, you could do that from your live database, selecting from your prep database, then insert from your created tables to your target table in your live database.
Previous Topic: LAS to Oracle Converter
Next Topic: Coordinate (X,Y) from RT90 to SWERF99 or WGS84 system.
Goto Forum:
  


Current Time: Thu Mar 28 15:21:32 CDT 2024