含XML列的Oracle表在SQL Developer及Power BI中无数据显示问题

问题背景 #

复制的测试库中，含XMLType列的DSI.SVC_RQST表在后端应用中能正常返回数据，但在SQL Developer（23.1.0.097）和Power BI里查询时返回空结果——即便仅查询非XML列也无法获取数据。已尝试SQL Developer的「工具-首选项-数据库-高级-在网格中显示XML值」设置及表别名，均无效。

环境信息 #

• SQL Developer版本：23.1.0.097
• Oracle数据库版本：19c Standard Edition 2 Release 19.0.0.0.0 - Production

表结构（DDL） #

CREATE TABLE "DSI"."SVC_RQST" 
   (    "RQS_ID" NUMBER DEFAULT NULL NOT NULL ENABLE, 
    "SBS_GUI" RAW(16) NOT NULL ENABLE, 
    "COM_TOK_GUI" RAW(16) NOT NULL ENABLE, 
    "FUN_PRM" "SYS"."XMLTYPE"  NOT NULL ENABLE, 
    "CON_PRM" "SYS"."XMLTYPE" , 
    "DAT_CRE" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
    "SER_GUI" RAW(16) NOT NULL ENABLE, 
     CONSTRAINT "SVC_RQST_PK" PRIMARY KEY ("RQS_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "BUTPROD_INDX"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "BUTPROD_DATA"   NO INMEMORY 
 XMLTYPE COLUMN "FUN_PRM" STORE AS SECUREFILE BINARY XML (
  TABLESPACE "BUTPROD_DATA" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES 
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA 
 XMLTYPE COLUMN "CON_PRM" STORE AS SECUREFILE BINARY XML (
  TABLESPACE "BUTPROD_DATA" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES 
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA 

示例数据 #

RQS_ID 2275473,
SBS_GUI 9D2749A0328E42DAB0D770495CD4D2AA,
COM_TOK_GUI 1F9A7BAC0EB08236E06301644B0A4EBB,
FUN_PRM <OrdersGet>
  <Service Name="OrdersGet"/>
  <Parameters>
    <Parameter Code="RestrictionCode" Value="5"/>
    <Parameter Code="XMLScope" Value="2"/>
    <Parameter Code="PurchasePrice" Value="N"/>
    <Parameter Code="SellingPrice" Value="N"/>
    <Parameter Code="CheckPointResponsible" Value="N"/>
    <Parameter Code="FirstTopElements" Value="30"/>
    <Parameter Code="ClearQueue" Value="Y"/>
  </Parameters>
  <Companies>
    <Company>
      <TranscodificationCode>ORDERS_GET</TranscodificationCode>
      <Code>ABT</Code>
      <ProfileCode>P18</ProfileCode>
      <MenuCode>DSI_TMS_ORD_GET</MenuCode>
      <EntityCode>ORDER</EntityCode>
      <Parameters>
        <Parameter Code="ThirdPartyCode" Value="P18"/>
      </Parameters>
      <Agencies>
        <Agency>
          <Code>01</Code>
        </Agency>
      </Agencies>
    </Company>
  </Companies>
</OrdersGet>    
DAT_CRE 14/08/2024  07:03:01,
SER_GUI 1F9CEFF33749BB94E06301644B0ADD47

解决方案 #

1. 修复XML列存储元数据 #

测试库复制过程中，SecureFile Binary XML的存储属性可能出现异常，重新执行存储定义修复：

ALTER TABLE DSI.SVC_RQST MODIFY COLUMN FUN_PRM STORE AS SECUREFILE BINARY XML (
  TABLESPACE "BUTPROD_DATA" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 BUFFER_POOL DEFAULT)
);

ALTER TABLE DSI.SVC_RQST MODIFY COLUMN CON_PRM STORE AS SECUREFILE BINARY XML (
  TABLESPACE "BUTPROD_DATA" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 BUFFER_POOL DEFAULT)
);

2. 刷新表统计信息 #

确保客户端工具能获取正确的表元数据：

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  OWNNAME => 'DSI',
  TABNAME => 'SVC_RQST',
  CASCADE => TRUE,
  ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
);

3. 临时查询方案（绕过XML解析） #

如果上述修复无效，查询时将XMLType转为字符串：

SELECT 
  RQS_ID,
  SBS_GUI,
  COM_TOK_GUI,
  FUN_PRM.getClobVal() AS FUN_PRM,
  CON_PRM.getClobVal() AS CON_PRM,
  DAT_CRE,
  SER_GUI
FROM DSI.SVC_RQST;

4. Power BI适配方案 #

Power BI对Oracle XMLType支持有限，创建视图封装转换后的列：

CREATE OR REPLACE VIEW DSI.V_SVC_RQST AS
SELECT 
  RQS_ID,
  SBS_GUI,
  COM_TOK_GUI,
  FUN_PRM.getClobVal() AS FUN_PRM,
  CON_PRM.getClobVal() AS CON_PRM,
  DAT_CRE,
  SER_GUI
FROM DSI.SVC_RQST;

之后Power BI连接该视图即可正常获取数据。

内容的提问来源于stack exchange，提问作者laoda