Oracle查询因REGEXP_SUBSTR生成子查询报ORA-00936错误的修正

ORA-00936错误的核心原因是REGEXP_SUBSTR的第一个参数格式非法：服务器生成的查询中，直接将返回多列的SELECT '010038659083008' i_policy_code_1 ,'010038658976008' i_policy_code_2 ,'010038483841008' i_policy_code_3 FROM DUAL作为REGEXP_SUBSTR的输入，但REGEXP_SUBSTR需要单个字符串值，而非多列查询结果；同时嵌套子查询未用括号包裹，Oracle无法识别合法表达式。

方案1：修正参数生成逻辑（推荐） #

服务器端生成参数时，直接将多个policy_code拼接成逗号分隔的单个字符串，而非生成多列SELECT语句。修正后的子查询如下：

AND cm.policy_code IN
(SELECT REGEXP_SUBSTR('010038659083008,010038658976008,010038483841008','[^,]+', 1, LEVEL)
 FROM DUAL
 CONNECT BY LEVEL <= REGEXP_COUNT('010038659083008,010038658976008,010038483841008',',') + 1)

该方式贴合原始查询设计逻辑，性能更稳定。

方案2：适配现有参数生成格式（临时兼容） #

若无法修改参数生成逻辑，需将多列查询结果转换为单个逗号分隔字符串后传入REGEXP_SUBSTR：

AND cm.policy_code IN
(SELECT REGEXP_SUBSTR(
          (SELECT LISTAGG(col, ',') WITHIN GROUP (ORDER BY col) 
           FROM (SELECT '010038659083008' col FROM DUAL UNION ALL
                 SELECT '010038658976008' col FROM DUAL UNION ALL
                 SELECT '010038483841008' col FROM DUAL)),
          '[^,]+', 1, LEVEL)
 FROM DUAL
 CONNECT BY LEVEL <= REGEXP_COUNT(
          (SELECT LISTAGG(col, ',') WITHIN GROUP (ORDER BY col) 
           FROM (SELECT '010038659083008' col FROM DUAL UNION ALL
                 SELECT '010038658976008' col FROM DUAL UNION ALL
                 SELECT '010038483841008' col FROM DUAL)),
          ',') + 1)

通过UNION ALL将多列转为多行，再用LISTAGG拼接成单个字符串，解决REGEXP_SUBSTR的输入格式问题。

额外优化建议 #

原始查询中重复调用REGEXP_SUBSTR的CONNECT BY逻辑可优化，避免重复计算：

AND cm.policy_code IN
(SELECT REGEXP_SUBSTR(:i_policy_code,'[^,]+', 1, LEVEL)
 FROM DUAL
 CONNECT BY LEVEL <= REGEXP_COUNT(:i_policy_code, ',') + 1)

用REGEXP_COUNT直接计算分隔符数量+1作为LEVEL上限，比原逻辑重复调用REGEXP_SUBSTR更高效。

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