Oracle中将逗号分隔值拆分为多行及ORA-30563报错解决问询

问题场景 #

需要将源数据转换为多行格式，使用regexp_substr结合层级查询实现时触发ORA-30563错误。

报错代码 #

With folder_query as 
(
    SELECT * FROM
    (SELECT ID, CardNo, REPLACE(LICENCE (RSN, 1), '<br/>', '') AS TagNo
      FROM FOLDER1) SQ
)
SELECT ID, CardNo,regexp_substr(TagNo,'[^,]+',1,level) AS TagNo
FROM folder_query
CONNECT by level <= REGEXP_COUNT(TagNo, '[^,]+')

报错信息（中文翻译） #

ORA-30563: 此处不允许使用外连接运算符 (+)
30563. 00000 - "outer join operator (+) is not allowed here"
*原因: 尝试在选择列表、CONNECT BY子句、START WITH子句或ORDER BY子句中引用(+)。
*操作: 不要在选择列表、CONNECT BY子句、START WITH子句或ORDER BY子句中使用该运算符。

问题原因 #

原层级查询未限制行的独立展开逻辑，Oracle在处理时会隐式尝试关联行，导致触发外连接运算符的错误检查。

修正后的代码 #

WITH folder_query AS (
    SELECT ID, CardNo, REPLACE(LICENCE(RSN, 1), '<br/>', '') AS TagNo
    FROM FOLDER1
)
SELECT ID, CardNo, REGEXP_SUBSTR(TagNo, '[^,]+', 1, LEVEL) AS TagNo
FROM folder_query
CONNECT BY LEVEL <= REGEXP_COUNT(TagNo, '[^,]+')
    AND PRIOR ID = ID
    AND PRIOR SYS_GUID() IS NOT NULL;

关键修正点 #

1. 简化嵌套子查询，去掉不必要的SELECT * FROM (...)层级
2. 在CONNECT BY中添加PRIOR ID = ID，确保仅对当前行进行拆分，不关联其他行
3. 添加PRIOR SYS_GUID() IS NOT NULL，通过生成唯一值避免循环引用问题，阻止Oracle隐式的连接逻辑

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