SQL Language (MOSC)

MOSC Banner

ORA-30002: SYS_CONNECT_BY_PATH function is not allowed here

edited Jan 18, 2011 4:13PM in SQL Language (MOSC) 2 commentsAnswered
  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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center