Moving XML over a DBLink

Question

I am trying to move some data over a dblink and one of the columns is an XMLType column. The code looks like this:

begin
    delete from some_schema.some_remote_tab@src_2_trg_dblink;
    INSERT INTO some_schema.some_remote_tab@src_2_trg_dblink(id, code, gen_date, xml_data)
    SELECT id, code, gen_date, xml_data
    FROM local_table;
end;

Oracle returns these errors:

ORA-02055: distributed update operation failed; rollback required
ORA-22804: remote operations not permitted on object tables or user-defined type columns

Some research on ORA-22804 shows that I am probably getting this error because of the XMLType column, but I am not sure how to resolve this.

(Oracle 10g)

Answer 1

Try to do this the other way around. That is log into the remote db, create a dblink to the local db, and do an insert like this

INSERT INTO remote_schema.some_remote_tab(id, code, gen_date, xml_data) 
SELECT id, code, gen_date, xml_data
    FROM local_table@dblink_to_local_db;