Oracle如何将XML格式CLOB转换为人类可读的VARCHAR格式

直接用Oracle原生XML处理函数解析binds_xml字段即可输出要求的NAME、POS、DATATYPE、VALUE四列结构化结果，无需额外开发自定义程序。

多行结构化结果查询 #

直接返回每个绑定变量单独一行的结果集，可直接关联其他查询使用：

SELECT
  bind_info.NAME,
  bind_info.POS,
  CASE bind_info.DATATYPE_CODE
    WHEN 1 THEN 'VARCHAR2'
    WHEN 2 THEN 'NUMBER'
    WHEN 12 THEN 'DATE'
    WHEN 96 THEN 'CHAR'
    WHEN 112 THEN 'CLOB'
    WHEN 113 THEN 'BLOB'
    WHEN 180 THEN 'TIMESTAMP'
    ELSE TO_CHAR(bind_info.DATATYPE_CODE)
  END AS DATATYPE,
  bind_info.VALUE
FROM
  v$sql_monitor mon,
  XMLTABLE(
    '/binds/bind'
    PASSING XMLTYPE(mon.binds_xml)
    COLUMNS
      NAME VARCHAR2(128) PATH '@name',
      POS NUMBER PATH '@pos',
      DATATYPE_CODE NUMBER PATH '@dty',
      VALUE VARCHAR2(4000) PATH 'value/text()'
  ) bind_info
WHERE
  mon.binds_xml IS NOT NULL;

单条CLOB格式拼接结果 #

如果需要把单条SQL对应的所有绑定变量拼接为单个CLOB字段返回，用XMLAGG实现（规避LISTAGG的长度限制）：

SELECT
  mon.sql_id,
  TO_CLOB(
    XMLAGG(
      XMLELEMENT(
        e,
        'NAME: ' || bind_info.NAME || 
        ', POS: ' || bind_info.POS || 
        ', DATATYPE: ' || CASE bind_info.DATATYPE_CODE
          WHEN 1 THEN 'VARCHAR2'
          WHEN 2 THEN 'NUMBER'
          WHEN 12 THEN 'DATE'
          WHEN 96 THEN 'CHAR'
          WHEN 112 THEN 'CLOB'
          WHEN 113 THEN 'BLOB'
          WHEN 180 THEN 'TIMESTAMP'
          ELSE TO_CHAR(bind_info.DATATYPE_CODE)
        END || 
        ', VALUE: ' || bind_info.VALUE,
        CHR(10)
      ).EXTRACT('/*')
    ).GETCLOBVAL()
  ) AS binds_structured
FROM
  v$sql_monitor mon,
  XMLTABLE(
    '/binds/bind'
    PASSING XMLTYPE(mon.binds_xml)
    COLUMNS
      NAME VARCHAR2(128) PATH '@name',
      POS NUMBER PATH '@pos',
      DATATYPE_CODE NUMBER PATH '@dty',
      VALUE VARCHAR2(4000) PATH 'value/text()'
  ) bind_info
WHERE
  mon.binds_xml IS NOT NULL
GROUP BY mon.sql_id, mon.binds_xml;

注意事项 #

• 上述代码适配v$sql_monitor默认的binds_xml结构：根节点为<binds>，每个绑定变量对应一个<bind>子节点，变量名、位置、类型编码存在<bind>节点属性中，变量实际值存在子节点<value>内
• 如果绑定变量值长度超过4000字节，将VALUE列定义修改为CLOB PATH 'value/text()'即可避免内容截断
• 类型映射部分可根据实际业务中遇到的数据类型编码自行补充，上述代码中已覆盖绝大多数常用数据类型

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