This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Mar 16, 2011 6:26 AM by laurent c. schneider Go to original post RSS
  • 15. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    MichaelS Guru
    Currently Being Moderated
    why this only seems to work with DBMS_RANDOM.VALUE?
    not only with dbms_random: sys_guid() would work as well and doesn't suffer from being repeated:
    SQL> with t as (
     select 1 id, '1,2,3,4' str from dual union all
     select 2 id, '5,6,7,8' str from dual
    )
    --
    --
    select id, regexp_substr(str, '\w+',1,level) str
      from t connect by prior id = id and prior sys_guid() is not null
    and level <= length(str) - length(replace(str, ',')) + 1
    /
                 ID STR       
    --------------- ----------
                  1 1         
                  1 2         
                  1 3         
                  1 4         
                  2 5         
                  2 6         
                  2 7         
                  2 8         
    
    8 rows selected.
  • 16. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    and sys_guid does not context-switch!
  • 17. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    munky Guru
    Currently Being Moderated
    laurent wrote:
    btw, connect by is old fashion, prefer recursive cte
    with t(str,tok) as 
    (select 'one,two,three,four', 'four' from dual
    union all
    select substr(str,instr(str,',')+1),substr(str,1,instr(str,',')-1) from t where str like '%,%')
    select tok from t;
    TOK               
    ------------------
    four              
    one               
    two               
    three             
    Thanks, I like that version. When I finally get them to upgrade to 11g, I'll bear that in mind.

    As I use the SQL version I showed previously for evaluating the return values of multi select lists in APEX, I wouldn't run into the problem iwth string lengths mentioned above - If a multi-select list is anywhere near that long, there wouldn't be something seriously wrong with the application design.

    As stated a few times now, I was not looking to use the technique first mentioned - I was seeking clarification as to why it worked, looks like curiosity on my part killed the cat in this case given the tone of your responses. Ah well.

    Cheers

    Ben
  • 18. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    laurent c. schneider Newbie
    Currently Being Moderated
    no worries, I just see those constructs too often in my db's... as I always said, the CONNECT BY row generator is just a hack, not a programming snippet
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points