oracle – 当列值中没有sys_connect_by_path定界符时,为什么会得

我在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;

一些观察：

• 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
• Leaving the value as “ninechars” and removing the last union statement,which is not connected to any of the other records,allows
  the query to work
• Changing the delimiter from ‘ -> ‘ to ‘ *> ‘ allows the query to work

题
ORA-30004错误的来源是什么？为什么Oracle认为分隔符是作为列值的一部分出现的？

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

解决方法

SQL> with t (id,label,parentid,reportlevel,fake_connect_by_path) as (
  2  select id,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

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!