This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Mar 16, 2011 6:26 AM by laurent c. schneider RSS

DBMS_RANDOM.value in hierarchical solution to string to table?

munky Guru
Currently Being Moderated
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
  • 1. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    Solomon Yakobson Guru
    Currently Being Moderated
    Munky wrote:


    I assumed that would essentially be the same as 'PRIOR 1 IS NOT NULL'
    Hierarchical queries do not allow loops. What is loop. Current row has a child which is also its ancestor. What is the definition for child which is also its ancestor? Good question. There is no definition in docs (at least I did not find any). It looks like it is same rows with same set of values. And that's what happens when you use:
    PRIOR 1 IS NOT NULL
    1 is added to each row and we will get same rows with same set of values. When we issue:
    PRIOR DBMS_RANDOM.VALUE IS NOT NULL
    we are adding different (random) value to each row in result set thus avoiding connect by loop.

    SY.
  • 2. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    munky Guru
    Currently Being Moderated
    Thanks Solomon, that makes sense now. However, does this mean that the error could still potentially occur even using DBMS_RANDOM then (ie. if the same value was returned twice in a row)?

    I take it from this, that the only reason for that 'PRIOR DBMS_RANDOM.VALUE IS NOT NULL' is to avoid the loop? I can't help thinking that there should be a slightly more elegant and less seemingly 'hacky' solution? I know there are quite a lot of different ways to achieve the same result, but I'm talking specifically about this hierarchical solution...

    Cheers

    Ben
  • 3. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    Solomon Yakobson Guru
    Currently Being Moderated
    Munky wrote:

    I take it from this, that the only reason for that 'PRIOR DBMS_RANDOM.VALUE IS NOT NULL' is to avoid the loop?
    Correct.
    I can't help thinking that there should be a slightly more elegant and less seemingly 'hacky' solution?
    Not aware of any. And yes, there is a theoretical chance of DBMS_RANDOM.VALUE producing duplicates.

    SY.
  • 4. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    laurent c. schneider Newbie
    Currently Being Moderated
    Hi,

    I dislike those kind of hacks, that may fail in any future patchset.

    As with function in General, you cannot know how many times a function will be called. Oracle Optimizer may one day decide that DBMS_RANDOM.VALUE = DBMS_RANDOM.VALUE. For tuning purpose...

    You entirely rely on an undocumented behavior.

    To convert the string '1,2,3,4' in 4 elements 1, 2, 3 and 4, use your fingers...
    SQL> CREATE OR REPLACE TYPE tn AS TABLE OF VARCHAR2 (4000)
      2  /
    
    Type created.
    
    SQL>
    SQL> CREATE FUNCTION f (str VARCHAR2, delim VARCHAR2 DEFAULT ',')
      2     RETURN tn
      3  IS
      4     ret   tn;
      5     c     NUMBER;
      6     i     NUMBER;
      7     s     VARCHAR2 (4000) := str;
      8  BEGIN
      9     ret := NEW tn ();
     10
     11     IF (str IS NOT NULL)
     12     THEN
     13        c := REGEXP_COUNT (str, delim) + 1;
     14        ret.EXTEND (c);
     15        i := 1;
     16
     17        WHILE (i <= c)
     18        LOOP
     19           ret (i) := REGEXP_SUBSTR (s, '[^,]*');
     20           s := REGEXP_REPLACE (s, '^[^,]*,');
     21           i:=i+1;
     22        END LOOP;
     23     END IF;
     24
     25     RETURN ret;
     26  END;
     27  /
    
    Function created.
    
    SQL>
    SQL> SELECT * FROM TABLE (f ('aaa,bbb,ccc,ddd', ','));
    
    COLUMN_VALUE
    ------------------------------------------------------------------------------
    aaa
    bbb
    ccc
    ddd
  • 5. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    munky Guru
    Currently Being Moderated
    Laurent

    As I stated in my previous reply, I am well aware of the various ways to convert a string to a table and I personally dislike the approach you have outlined (mostly cause it's slow).
    >
    Oracle Optimizer may one day decide that DBMS_RANDOM.VALUE = DBMS_RANDOM.VALUE. For tuning purpose...
    >
    I doubt it.
    >
    To convert the string '1,2,3,4' in 4 elements 1, 2, 3 and 4, use your fingers...
    >
    Thanks for that most helpful suggestion.
    >
    You entirely rely on an undocumented behavior.
    >
    No, I'm not relying on anything - I was asking why it worked, that's all.

    Cheers

    Ben
  • 6. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    Solomon Yakobson Guru
    Currently Being Moderated
    laurent wrote:
    Hi,

    I dislike those kind of hacks, that may fail in any future patchset.
    Absolutely. And not just "may". If I recall correctly, in some version(s) dbms_random.value did not work but dbms_random.random did. I prefer:
    SQL> WITH t AS (SELECT   '1,2,3,4' str FROM DUAL)
      2  SELECT  str,
      3          REGEXP_SUBSTR (str,
      4                         '[^,]+',
      5                         1,
      6                         COLUMN_VALUE
      7                        )
      8                       AS single_element,
      9         COLUMN_VALUE AS element_no
     10    FROM  t,
     11          TABLE(
     12                CAST(
     13                     MULTISET(
     14                              SELECT  LEVEL
     15                                FROM  DUAL
     16                                CONNECT BY INSTR(str,',',1,LEVEL - 1) > 0
     17                             )
     18                     AS sys.OdciNumberList
     19                    )
     20               )
     21  /
    
    STR     SINGLE_ ELEMENT_NO
    ------- ------- ----------
    1,2,3,4 1                1
    1,2,3,4 2                2
    1,2,3,4 3                3
    1,2,3,4 4                4
    
    SQL> 
    SY.
  • 7. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    munky Guru
    Currently Being Moderated
    Solomon

    I still prefer the pure SQL version...
    WITH t AS (SELECT   '1,2,3,4' str FROM DUAL)
    , t_str AS
        (
        SELECT  str||','                                              AS str,
         (LENGTH(str) - LENGTH(REPLACE(str,','))) + 1 AS no_of_elements
         FROM    t
        ),   t_n_rows AS
        (
         SELECT  LEVEL AS i
         FROM    dual
         CONNECT BY LEVEL <= (SELECT SUM(no_of_elements) FROM t_str)
        ),   t_in_list AS
        (
         SELECT SUBSTR(str,start_pos,(next_pos - start_pos)) AS element_value
         FROM   (
         SELECT t_str.str,
         nt.i AS element_no,
         INSTR(t_str.str,',',DECODE(nt.i,1,0,1),
         DECODE(nt.i,1,1,nt.i - 1)) + 1 AS start_pos,
         INSTR(t_str.str,',',1,DECODE(nt.i,1,1,nt.i))       AS next_pos
         FROM   t_str JOIN t_n_rows nt
         ON nt.i <= t_str.no_of_elements
         )
        )
        SELECT * 
        FROM t_in_list
    I seem to remember this being faster when I first tested all the different ways I could find back in the day.

    Cheers

    Ben
  • 8. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    laurent c. schneider Newbie
    Currently Being Moderated
    The solution with connect by is truely faster for long strings
    set timi on echo on;
    
    WITH t AS (SELECT   '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,'||
    '501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000' str FROM DUAL)
    , t_str AS
        (
        SELECT  str||','                                              AS str,
         (LENGTH(str) - LENGTH(REPLACE(str,','))) + 1 AS no_of_elements
         FROM    t
        ),   t_n_rows AS
        (
         SELECT  LEVEL AS i
         FROM    dual
         CONNECT BY LEVEL <= (SELECT SUM(no_of_elements) FROM t_str)
        ),   t_in_list AS
        (
         SELECT SUBSTR(str,start_pos,(next_pos - start_pos)) AS element_value
         FROM   (
         SELECT t_str.str,
         nt.i AS element_no,
         INSTR(t_str.str,',',DECODE(nt.i,1,0,1),
         DECODE(nt.i,1,1,nt.i - 1)) + 1 AS start_pos,
         INSTR(t_str.str,',',1,DECODE(nt.i,1,1,nt.i))       AS next_pos
         FROM   t_str JOIN t_n_rows nt
         ON nt.i <= t_str.no_of_elements
         )
        )
        SELECT count(*) 
        FROM t_in_list;
        FROM t_in_list
             *
    ERROR at line 27:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00910: specified length too long for its datatype
    
    
    Elapsed: 00:00:00.17
    
     SELECT count(*) FROM TABLE (f ( '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,'||
     '501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000'));
    
    
      COUNT(*)
    ----------
          1000
    
    Elapsed: 00:00:00.62
    but apparently you prefer random programming than plsql ;-)

    Edited by: laurent on Mar 11, 2011 2:03 PM

    I do admire the work of Billington. Will I therefore blindly run any hack in my production system?
  • 9. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    munky Guru
    Currently Being Moderated
    >
    I do admire the work of Billington. Will I therefore blindly run any hack in my production system?
    >
    At what point did I say that I wanted to run this in a production system? As I have stated earlier, I was merely curious as to why it worked - I'm not sure why you don't seem to be able to understand this...

    Cheers

    Ben
  • 10. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    laurent c. schneider Newbie
    Currently Being Moderated
    LOL !

    Ok, here is my explanation :

    To detect a loop, Oracle compare the prior value with the prior of all ancestors.

    This buggy design introduced in 8.1.7.4.0 leaded in having non-detected loop that could either crash or be abused by some clever developer to generate infinite loops
  • 11. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    laurent c. schneider Newbie
    Currently Being Moderated
    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             
  • 12. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    laurent c. schneider Newbie
    Currently Being Moderated
    and it is much faster btw
    with t(str,tok) as 
    (select 
    cast(
    '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,'||
    '501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000' 
     as varchar2(4000)), cast('1000' as varchar2(4000))from dual
    union all
    select substr(str,instr(str,',')+1),substr(str,1,instr(str,',')-1) from t where str like '%,%')
    select count(*) from t;
    
      COUNT(*)
    ----------
          1000
    
    Elapsed: 00:00:00.03
  • 13. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    laurent c. schneider Newbie
    Currently Being Moderated
    At what point did I say that I wanted to run this in a production system?
    I would not grant you access to my production system anyway :-(
  • 14. Re: DBMS_RANDOM.value in hierarchical solution to string to table?
    laurent c. schneider Newbie
    Currently Being Moderated
    Oracle Optimizer may one day decide that DBMS_RANDOM.VALUE = DBMS_RANDOM.VALUE. For tuning purpose...
    I doubt it.
    Just to clear your doubts
    select * from dual where DBMS_RANDOM.VALUE = DBMS_RANDOM.VALUE
    
    D
    -
    X
1 2 Previous Next

Legend

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