Hi,
I quite often use this technique for tokenizing a string into rows:

With tst As (  
Select 'row1' r, 'Hello,World,Test' d From Dual  
)  
Select r, Regexp_Substr(d,'[^,]+',1,level) From tst  
Connect By Instr(d,',',1,level-1) > 0;  

however if I have multiple values for r, i.e:

With tst As (  
Select 'row1' r, 'Hello,World,Test' d From Dual  
Union All  
Select 'row2' r, 'Some,Other,Row' d From Dual  
)  
Select r, Regexp_Substr(d,'[^,]+',1,level) From tst  
Connect By Instr(d,',',1,level-1) > 0; 

then I get duplicate values rreturned. If I put in And Prior v.r = v.r then I get a cyclic warning.

Somebody suggested that I modify my query to the following:

With tst As (  
Select 'row1' r, 'Hello,World,Test' d From Dual  
Union All  
Select 'row2' r, 'Some,Other,Row' d From Dual  
)  
Select r, Regexp_Substr(d,'[^,]+',1,level) From tst  
Connect By Instr(d,',',1,level-1) > 0
And Prior r=r
And Prior sys_guid() Is Not Null;

Which works perfectly. However I really don't like using something without knowing Why this fixes the solution? From what I've read sys_guid() simply returns a unique identifier - so why does it eliminate the duplicate rows? Is this because using level in this way is effectively exploiting an unintended feature of the construct? I think you have alluded to that in the past in another post (and if that is indeed the issue then would you advise against using this method at all?)? Do we just need a function which returns a unique value for every row? If so then although you would of course never do it, the following would work most of the time.
And prior dbms_random.value() is not null?

Can you elaborate on this?

Many thanks,
John