ORA-30002: SYS_CONNECT_BY_PATH function is not allowed here
Hi,
i have the following query that works from 9i to 11.2.0.1 but it fails on 11.2.0.2 with
ORA-30002: SYS_CONNECT_BY_PATH function is not allowed here
Any ideas as to why? Or is it a bug that causes the failure?
Regards,
Sha
SELECT Nvl(LTrim(Max(sys_connect_by_path(sid,',')),','),'ALL COMPLIED') sid
FROM (SELECT sid,ROWNUM rn
FROM gv$session
WHERE username = Sys_Context('USERENV','SESSION_USER')
AND process NOT IN (SELECT process
FROM gv$session
WHERE audsid = Sys_Context('USERENV','SESSIONID')
)
)
START WITH rn = 1
CONNECT BY PRIOR rn + 1 = rn
ORDER SIBLINGS BY sid
ORA-30002: SYS_CONNECT_BY_PATH function is not allowed here
Any ideas as to why? Or is it a bug that causes the failure?
Regards,
Sha
SELECT Nvl(LTrim(Max(sys_connect_by_path(sid,',')),','),'ALL COMPLIED') sid
FROM (SELECT sid,ROWNUM rn
FROM gv$session
WHERE username = Sys_Context('USERENV','SESSION_USER')
AND process NOT IN (SELECT process
FROM gv$session
WHERE audsid = Sys_Context('USERENV','SESSIONID')
)
)
START WITH rn = 1
CONNECT BY PRIOR rn + 1 = rn
ORDER SIBLINGS BY sid
0