Hi Experts,
I know question is very simple, but just trying to learn things clearly...
I'm not able to get the use of <tt>SYS_GUID</tt> here.
It seems like it's used to generate some unique id but why <tt>'PRIOR SYS_GUID()'</tt> and that too checking for <tt>'IS NOT NULL'</tt>?
WITH xx AS
(
SELECT 1 id, 'A,B' str FROM DUAL
UNION ALL
SELECT 2 id, 'C,D' str FROM DUAL
--UNION ALL
--SELECT 2 id, 'B,A,E,E,E,D,F' str FROM DUAL
)
select
id, regexp_substr(str,'[^,]',1,level), level, SYS_GUID()
from xx
connect by level <= length(replace(str,','))
and PRIOR id = id
and PRIOR SYS_GUID() is not null /* on commenting this getting err : "ORA-01436: CONNECT BY loop in user data" */
order by id, level;
Output :
1 A 1 DA607FA725E21435E0440003BA095435
1 B 2 DA607FA725E41435E0440003BA095435
2 C 1 DA607FA725E61435E0440003BA095435
2 D 2 DA607FA725E81435E0440003BA095435
Can any body please explain me this?
Oracle version: Oracle Database 10g Enterprise Edition Release *10.2.0.5.0* - 64bi