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)

score 2 · Answer 1 · answered Jun 08 '11 at 04:45

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.

I'd already tried the `xmltype ( xml_data.asClobVal() )`, but it didn't work, probably because the result of the expression is still an `XMLType`. The biggest problem will be converting the text BACK to XML on the remote side - it's a vendor-owned schema... which is why I was trying to do everything from my side, and not theirs. @bpgergo's solution might work best, since I can run it from a script that logs in to their database, and the DBLink is already set up. Of course, it *does* add another manual step to our process... — FrustratedWithFormsDesigner, Jun 08 '11 at 15:25

score 1 · Answer 2 · answered Apr 22 '15 at 03:52

Instead Perform a Data PULL.

create the data pull procedure at Remote database B.
create synonyms and provide grants to the dblink user.
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;

score 1 · Accepted Answer · answered Jun 07 '11 at 16:17

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;

answered Jun 07 '11 at 16:17

bpgergo

15,669
5
44
68

It will add another manual step to the process, but this looks like the best way to do it. Short of getting Oracle to allow XMLTypes to be pused over a dblink... – FrustratedWithFormsDesigner Jun 09 '11 at 14:27

score 0 · Answer 4 · answered Feb 19 '16 at 16:14

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.

AttilaK · Answer 5 · 2016-08-08T16:59:33.013

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*/);

Moving XML over a DBLink

5 Answers5