当列值中不存在sys_connect_by_path分隔符时，为什么会出现Ora-30004 [英] Why do I get Ora-30004 when sys_connect_by_path delimiter is not present in column values

问题描述

我已经在Oracle版本上对此进行了测试:
11.2.0.3.0
12.1.0.2.0

I have tested this on Oracle versions:
11.2.0.3.0
12.1.0.2.0

以下查询引发ORA-30004错误，但我的定界符->"未在任何列值中使用:

The following query throws an ORA-30004 error, but my delimiter of ' -> ' is not used in any of the column values:

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"也会破坏查询
  • 似乎这里的所有9个字符值都打破了查询
    
    

• Changing the value "ninechars" to "ninecharsx" allows the query to work
• Changing the value "ninechars" to "abcdefghi" also breaks the query
  • It seems like all nine character values here break the query
    
    

问题
ORA-30004错误的根源是什么? Oracle为什么认为分隔符出现在列值中?

Question
What is the source of the ORA-30004 error? Why does Oracle think that the delimiter is appearing as part of a column value?

感谢bobdylan提供的 pastebin.com/Ad1edFcJ 链接留在了有助于说明问题的注释中

Thanks to bobdylan for this pastebin.com/Ad1edFcJ link left in the comments that helps illustrate the issue

推荐答案

这闻起来像个bug.如果您需要解决它并实现您的逻辑，或者可以使用递归子查询分解(recursive With)，在11.2.0.4中可以正常使用

This smells like a bug. If you need to workaround it and implement your logic, alternatively you can use recursive subquery factoring (recursive With), which works alright in 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

这篇关于当列值中不存在sys_connect_by_path分隔符时，为什么会出现Ora-30004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！