时间戳

首页 > 解决方案 > 将 Oracle XMLTYPE 数据迁移到 Postgres

postgresql - 将 Oracle XMLTYPE 数据迁移到 Postgres

问题描述

我在 Oracle 中有一个包含 XMLTYPE 数据类型的表。谁能建议一种将这些数据提取到文件中然后将其加载到 Postgres 中的方法？作为从 Oracle 到 Postgres 的数据迁移的一部分，我正在努力寻找一种方法。不幸的是，源和目标之间没有连接，所以我不能使用任何外部表。使用 ora2pg 也是不行的，因为我们可能会有数百万条它无法执行的记录。

我已将以下设置为测试场景，但我无法获取数据：

CREATE TABLE xml_test (id number, xml xmltype);
INSERT INTO xml_test VALUES (1, XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>'));
INSERT INTO xml_test VALUES (11, XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>SOME TEXT</chapter></book>'));
INSERT INTO xml_test VALUES (111, XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>"SOME MORE TEXT"</chapter></book>'));
INSERT INTO xml_test values (2, xmltype.createxml('<subject><name>test</name><list><li>a</li><li>b</li></list></subject>'));
INSERT INTO xml_test values (3, xmltype.createxml('<subject><name>test</name><list><li>a</li></list></subject>'));
INSERT INTO xml_test VALUES 
  (4, xmltype('<?xml version="1.0"?>
  <Warehouse>
    <WarehouseId>1</WarehouseId>
    <WarehouseName>Southlake, Texas</WarehouseName>
    <Building>Owned</Building>
    <Area>25000</Area>
    <Docks>2</Docks>
    <DockType>Rear load</DockType>
    <WaterAccess>true</WaterAccess>
    <RailAccess>N</RailAccess>
    <Parking>Street</Parking>
    <VClearance>10</VClearance>
  </Warehouse>'));

现在我的 Oracle 表中有这些数据，将其迁移到我的 Postgres 数据库的最佳方法是什么？

标签： postgresqloracledata-migrationxmltype

解决方案

一种方法是将 XML 数据编码为 base64。然后将其导出为 csv 文件并将其重新导入 Postgres 数据库。

编码base64的函数：

CREATE OR REPLACE FUNCTION get_base64( fil  IN CLOB )
RETURN CLOB
AS
   res CLOB;
   buf VARCHAR2(19200);
   pos PLS_INTEGER := 1;
   amt PLS_INTEGER := 19200;
   b64 VARCHAR2(32767);
BEGIN
   DBMS_LOB.CREATETEMPORARY( res, FALSE );
   DBMS_LOB.OPEN( res, DBMS_LOB.LOB_READWRITE );
   LOOP
      DBMS_LOB.READ( fil, amt, pos, buf );
      pos := pos + amt;
      b64 := UTL_RAW.CAST_TO_VARCHAR2( UTL_ENCODE.BASE64_ENCODE( UTL_RAW.CAST_TO_RAW( buf )));
      DBMS_LOB.WRITEAPPEND( res, LENGTH( b64 ), b64 );
   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_LOB.CLOSE( res );
      RETURN res;
END get_base64;
/

导出为 csv：

DECLARE
   l_file UTL_FILE.FILE_TYPE;
BEGIN
   l_file := UTL_FILE.FOPEN(location => 'TEMP_DIR', filename => 'test.csv', open_mode => 'w', max_linesize => 32767);
   UTL_FILE.PUT_LINE( l_file, '"id"; "xml_base64"' );
   
   FOR cur IN ( SELECT id,
                       get_base64(xmltype.getclobval(xml)) AS xml_base64
                  FROM xml_test )
   LOOP
      UTL_FILE.PUT_LINE( l_file, '"'||cur.id ||'"; "' || cur.xml_base64 ||'"' );
   END LOOP;
   UTL_FILE.FCLOSE( l_file );
EXCEPTION WHEN OTHERS THEN 
   UTL_FILE.FCLOSE( l_file );
END;
/

将其导入 Postgres。您需要一个额外的文本列。然后解码 base64 编码的文本并将其解析为 XML。

UPDATE xml_test
   SET xml = XMLPARSE(DOCUMENT convert_from(decode(xml_base64, 'base64'), 'UTF8'));

这应该有效。请检查它是否也足够性能。

推荐阅读