为什么当列值中不存在分隔符时sys_connect_by_path我会收到 Ora-30004

Question

>我已经在Oracle版本上测试过了：
11.2.0.3.0
12.1.0.2.0

以下查询抛出 ORA-30004 错误，但我的分隔符 ' -> ' 未在任何列值中使用：

with temptable as (
  select '2624' as id, 'ninechars' as label, '' as parentid from dual union 
  select '2625' as id, 'erewrettt' as label, '2624' as parentid from dual union 
  select '2626' as id, 'Im stumped' as label, '' as parentid from dual union 
  select '2627' as id, '- Unknown -' as label, '' as parentid from dual
)
select sys_connect_by_path(label, ' -> ' ) 
from temptable
start with parentid is null 
connect by prior id = parentid;

一些观察：

• 将值"ninechars"更改为"ninecharsx"允许查询工作
• 将值"ninechars"更改为"abcdefghi"也会中断查询
  • 似乎这里的所有九个字符值都中断了查询
    
    
• 将值保留为"ninechars"并删除最后一个联合语句（未连接到任何其他记录）允许 要工作的查询
• 将分隔符从 ' -> ' 更改为 ' *> ' 允许查询工作

问题
ORA-30004 错误的来源是什么？ 为什么 Oracle 认为分隔符显示为列值的一部分？

编辑：感谢bobdylan在评论中留下的这个 pastebin.com/Ad1edFcJ 链接，有助于说明问题

Answer 1

这闻起来像一个错误。如果您需要解决它并实现您的逻辑，或者您可以使用递归子查询分解（递归 With），它在 11.2.0.4 中工作正常：

SQL> with t (id, label, parentid, reportlevel, fake_connect_by_path) as (
  2  select id, label, parentid, 0 as reportlevel, ' -> ' || label as fake_connect_by_path
  3    from temptable
  4   where parentid is null
  5   union all
  6  select tt.id, tt.label, tt.parentid, reportlevel + 1, t.fake_connect_by_path || ' -> ' || tt.label as fake_connect_by_path
  7    from temptable tt
  8    join t on t.id = tt.parentid
  9  )
 10  select fake_connect_by_path
 11    from t;
FAKE_CONNECT_BY_PATH
--------------------------------------------------------------------------------
 -> ninechars
 -> Im stumped
 -> - Unknown -
 -> ninechars -> erewrettt