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

Instead Perform a Data PULL.

1. create the data pull procedure at Remote database B.
2. create synonyms and provide grants to the dblink user.
3. Call the Remote procedure from Database A (Source) Perform a commit at Database A(source).

(Meanwhile .. wait for oracle to find some solution to perform the PUSH of XML over dblink in the future)

Create a procedure at Remote site Database B

CREATE OR REPLACE PROCEDURE PR_REMOTE(OP_TOTAL_COUNT OUT NUMBER) IS
BEGIN

  INSERT /*+ DRIVING_SITE(src) */
  INTO REMOTE_TABLE TGT_B
    (XMLDATA_COL)
    SELECT SRC.XMLDATA FROM LOCAL_TABLE@TGT2SRC_DBLINK SRC;

  OP_TOTAL_COUNT := SQL%ROWCOUNT;

END;

Call the procedure from Database A

DECLARE
  V_COUNT NUMBER := 0;
BEGIN
  PR_REMOTE(V_COUNT);
  COMMIT;
END;

Answer 2

I was facing the same issue with an heterogeneous DB link to SQL server.

Ended up using xmltype.getStringVal() to insert in a VARCHAR column on SQL Server side as the data was under 4000 characters.

There is also xmltype.getClobVal() if over 4000 characters but I haven't tested it.

Answer 3

We get ORA-22804 because every instance of a Type in our Oracle database has an OID, which is unique within the database. We cannot transfer that OID to another database; this has caused me grief before when trying to import schemas which have User-Defined Types. I hadn't realised that it also affected XMLType, but it is an Object so it is not surprising.

The solution is icky: you will have to unload the XML into text on your local database and then convert it back into XML in the remote database.

I don't have a distributed DB set-up to test this right now, but if you're lucky it may work:

INSERT INTO some_schema.some_remote_tab@src_2_trg_dblink(id, code, gen_date, xml_data)
SELECT id, code, gen_date, xmltype ( xml_data.asClobVal() )
FROM local_table;

If the asClobVal() method doesn't work you may need to use the SQL function XMLSERIALIZE() instead.

XMLSerialize(DOCUMENT xml_data AS CLOB) 

If you're really unlucky you won't be able to do this in a single SQL statement, and you'll have to solve it using PL/SQL. To a certain extent this will depend on which version of the database you are using; the more recent the version, the more likely you'll be able to it in SQL rather than PL/SQL.

Answer 4

The "xml->text->xml" chain might be complicated, but could help in some cases (for example when inserting is not on option but updating only). You can try with "n" peaces of varchar columns (in the destination table or in a differnet one, perheaps in different schema on the remote DB), where "n" is: ceil(max(dbms_lob.getlength(MyXmlColumn)) / 4000)

Then you can transfer these fragments to remote temporary fields:

insert into RemoteSchema.MyTable(Id, XmlPart1, XmlPart2,...)
(select 1 /*some Id*/,
        dbma_lob.substr(MyXmlColumn.getclobval(), 4000, 1),
        dbma_lob.substr(MyXmlColumn.getclobval(), 4000, 4001),
        ...
 from LocalSchema.MyTable

XmlType can be re-composed from fragments like this:

create or replace function concat_to_xml(p_id number)
return xmltype
is
  xml_lob clob;
  xml xmltype;
begin
  dbms_lob.createtemporary(xml_lob, true);
  for r in (select XmlPart1, XmlPart2, ... from RemoteSchema.MyTable where Id = p_id)
  loop
    if r.XmlPart1 is not null then
      dbms_lob.writeappend(xml_lob, length(r.XmlPart1), r.XmlPart1);
    end if;
    if r.XmlPart2 is not null then
      dbms_lob.writeappend(xml_lob, length(r.XmlPart2), r.XmlPart2);
    end if;
    ...
  end loop;
  xml := xmltype(xml_lob);
  dbms_lob.freetemporary(xml_lob);
  return xml;
end;

Finally use the result to update any other table in the remothe schema like:

update RemoteSchema.MyTable2 t2 set t2.MyXmlColumn = concat_to_xml(1 /*some Id*/);

Answer 5

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;