SQL & PL/SQL

Hi

From the example titled 'hierarchical solution' shown in this page...

http://www.oracle-developer.net/display.php?id=412

Mohit Agarwal's code would be as follows...

WITH t AS (SELECT   '1,2,3,4' str FROM DUAL)
    SELECT   str, REGEXP_SUBSTR (str,
                                 '[^,]+',
                                 1,
                                 LEVEL)
                     AS single_element, LEVEL AS element_no
      FROM   (    SELECT   ROWNUM AS id, str FROM t)
CONNECT BY       INSTR (str,
                        ',',
                        1,
                        LEVEL - 1) > 0
             AND id = PRIOR id
             AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

I really don't understand why this only seems to work with DBMS_RANDOM.VALUE?

I assumed that would essentially be the same as 'PRIOR 1 IS NOT NULL' but if I do that I encounter 'ORA-01436: CONNECT BY loop in user data'. I have had a Google and thought that maybe it could have something to do with the PRAGMA restrict_references in DBMS_RANDOM. I tried this in my own package that just returned a 1 but still got the CONNECT BY loop error.

Am I missing something very obvious here - what is special about DBMS_RANDOM in this case and can I replicate it in my own package?

Apologies if I'm being thick, but it's Friday.

Cheers

Ben