通过 DBLink 移动 XML

Question

我正在尝试通过 dblink 移动一些数据，其中一列是 XMLType 列。代码如下所示：

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 返回这些错误：

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

对 ORA-22804 的一些研究表明，我可能由于 XMLType 列而收到此错误，但我不确定如何解决此问题。

（甲骨文10g）

Answer 1

我们得到 ORA-22804 是因为 Oracle 数据库中类型的每个实例都有一个 OID，该 OID 在数据库中是唯一的。我们无法将该 OID 传输到另一个数据库；之前在尝试导入具有用户定义类型的模式时，这让我感到悲伤。我没有意识到它也影响了 XMLType，但它是一个对象，所以这并不奇怪。

解决方案很棘手：您必须将 XML 卸载为本地数据库上的文本，然后将其转换回远程数据库中的 XML。

我现在没有分布式数据库设置来测试这个，但如果你幸运的话它可能会起作用：

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;

如果 asClobVal() 方法不起作用，你可能需要使用改为 SQL 函数 XMLSERIALIZE()。

XMLSerialize(DOCUMENT xml_data AS CLOB) 

如果您真的很不幸，您将无法在单个 SQL 语句中完成此操作，并且您必须使用 PL/SQL 来解决它。在某种程度上，这取决于您使用的数据库版本；版本越新，您就越有可能在 SQL 而不是 PL/SQL 中使用它。

Answer 2

尝试以相反的方式进行此操作。即登录到远程数据库，创建到本地数据库的数据库链接，然后执行如下插入

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;

Answer 3

而是执行数据拉取。

1. 在远程数据库中创建数据拉取过程 B.
2. 创建同义词并向 dblink 用户提供授权。
3. 从数据库 A（源）调用远程过程 在数据库 A（源）执行提交。

（同时..等待oracle找到一些解决方案来在将来通过dblink执行XML的PUSH）

在远程站点数据库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;

从数据库A调用该过程

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

Answer 4

我在连接到 SQL Server 的异构数据库链接时遇到了同样的问题。

由于数据少于 4000 个字符，最终使用 xmltype.getStringVal() 插入 SQL Server 端的 VARCHAR 列。

如果超过 4000 个字符，还有 xmltype.getClobVal() 但我还没有测试过。

Answer 5

"xml->text->xml" 链可能很复杂，但在某些情况下可能会有所帮助（例如，当不能选择插入而只能更新时）。
您可以尝试使用“n”个 varchar 列（在目标表中或在不同的表中，可能在远程数据库上的不同架构中），其中“n”是：
ceil(max(dbms_lob.getlength(MyXmlColumn)) / 4000)

然后您可以将这些片段传输到远程临时字段：

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 可以从片段重新组合，如下所示：

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;

最后使用结果更新任何remothe 模式中的其他表如：

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