Oracle SQL中REGEXP_COUNT报ORA-19011缓冲区过小问题求解

执行以下SQL语句时，未启用WHERE子句可正常返回结果，但启用WHERE regexp_count( C1.column_value,'<') < 2过滤条件后，在较大日期范围下抛出ORA-19011: Character string buffer too small错误，缩小日期范围则可正常执行：

with q1 ( Tdata,Key) AS (
    SELECT  (XMLtype(pint.transportdata, nls_charset_id('AL32UTF8')))
    , PINT.PAYMENTINTERCHANGEKEY
  from bph_owner.paymentinterchange pint
  where         PINT.TRANSPORTTIME >= to_date('2024-01-11 00:00:00', 'yyyy-mm-dd hh24:mi:Ss')
       AND  PINT.TRANSPORTTIME < to_date('2024-04-12 00:00:00', 'yyyy-mm-dd hh24:mi:Ss')
       AND  LENGTH(pint.transportdata)>0
       AND PINT.FILEFORMAT like 'pain%'
  )
    SELECT  C1.column_value , q1.Key from q1,
  XMLTABLE(
     '//*'
           PASSING q1.Tdata
              )   C1
  --where regexp_count( C1.column_value,'<') < 2
      ;

解决方案 #

1. 限制XML节点提取范围（推荐） #

原XMLTABLE的//*会提取所有XML节点，包括包含超大内容的节点（如CDATA块、长文本节点），这类节点转换为字符串时会超出REGEXP_COUNT的处理缓冲区。修改XPath表达式，只提取叶子节点（无嵌套子节点的节点），避免处理大文本节点：

with q1 ( Tdata,Key) AS (
    SELECT  (XMLtype(pint.transportdata, nls_charset_id('AL32UTF8')))
    , PINT.PAYMENTINTERCHANGEKEY
  from bph_owner.paymentinterchange pint
  where         PINT.TRANSPORTTIME >= to_date('2024-01-11 00:00:00', 'yyyy-mm-dd hh24:mi:Ss')
       AND  PINT.TRANSPORTTIME < to_date('2024-04-12 00:00:00', 'yyyy-mm-dd hh24:mi:Ss')
       AND  LENGTH(pint.transportdata)>0
       AND PINT.FILEFORMAT like 'pain%'
  )
    SELECT  C1.column_value , q1.Key from q1,
  XMLTABLE(
     '//*[not(*)]'  -- 仅提取叶子节点
           PASSING q1.Tdata
              )   C1
      ;

2. 截断超长节点文本（适配允许截断的场景） #

若业务允许忽略超长文本的部分内容，可先截断C1.column_value再执行REGEXP_COUNT，避免缓冲区溢出：

with q1 ( Tdata,Key) AS (
    SELECT  (XMLtype(pint.transportdata, nls_charset_id('AL32UTF8')))
    , PINT.PAYMENTINTERCHANGEKEY
  from bph_owner.paymentinterchange pint
  where         PINT.TRANSPORTTIME >= to_date('2024-01-11 00:00:00', 'yyyy-mm-dd hh24:mi:Ss')
       AND  PINT.TRANSPORTTIME < to_date('2024-04-12 00:00:00', 'yyyy-mm-dd hh24:mi:Ss')
       AND  LENGTH(pint.transportdata)>0
       AND PINT.FILEFORMAT like 'pain%'
  )
    SELECT  C1.column_value , q1.Key from q1,
  XMLTABLE(
     '//*'
           PASSING q1.Tdata
              )   C1
  where regexp_count( SUBSTR(C1.column_value, 1, 4000), '<') < 2
      ;

注：Oracle默认VARCHAR2最大长度为4000字节，12c及以上版本可扩展至32767字节，可根据实际情况调整截断长度。

3. 使用XML原生判断替代正则匹配 #

利用XML自身的函数替代字符串正则匹配，避免大文本转换为字符串的过程：

with q1 ( Tdata,Key) AS (
    SELECT  (XMLtype(pint.transportdata, nls_charset_id('AL32UTF8')))
    , PINT.PAYMENTINTERCHANGEKEY
  from bph_owner.paymentinterchange pint
  where         PINT.TRANSPORTTIME >= to_date('2024-01-11 00:00:00', 'yyyy-mm-dd hh24:mi:Ss')
       AND  PINT.TRANSPORTTIME < to_date('2024-04-12 00:00:00', 'yyyy-mm-dd hh24:mi:Ss')
       AND  LENGTH(pint.transportdata)>0
       AND PINT.FILEFORMAT like 'pain%'
  )
    SELECT  C1.column_value , q1.Key from q1,
  XMLTABLE(
     '//*'
           PASSING q1.Tdata
              )   C1
  WHERE XMLExists('not(contains(., "<"))' PASSING C1.column_value)
      ;

4. 临时增大会话缓冲区（应急方案） #

针对Oracle 12cR2及以上版本，可临时增大会话级字符串缓冲区参数：

ALTER SESSION SET PLSQL_STRING_MAX_SIZE = 32767;

此方案仅临时缓解，若存在超32767字节的节点仍会报错，建议优先使用前三种方案。

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