Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 -------------------------------- Quadruplet Puzzle --------------------------------------- The following Quadruplet Puzzle Query Solution can solve "4" different puzzles in a single ORACLE SQL. Puzzle 1: Largest Rectangle of Words find the largest possible rectangle of letters such that every row forms a word (right to left) and every column forms a word (reading top to bottom) Puzzle 2: blank SUDOKU Solution Puzzle 3: Impossible_Combinations What is the smallest number of coins that you can’t make a dollar with? i.e., for what N does there not exist a set of N coins adding up to a dollar? It is possible to make a dollar with 1 current U.S. coin (a Susan B. Anthony), 2 coins (2 fifty cent pieces), 3 coins (2 quarters and a fifty cent piece), etc. It is not possible to make exactly a dollar with 101 coins. Puzzle 4: Divisible by 1 to 9 Find a 9-digit number a[1]a[2]a[3]…. containing all the digits 1,2,…,9 such that “K” divides a[1]a[2]a[3]…..a[k] for each k = 1,2,3….9. CREATE TABLE IOT3 (len, str, PRIMARY KEY (len, str ) ) ORGANIZATION INDEX AS SELECT length('SUND') as len, 'SUND' as str FROM dual union all SELECT length('MOND') as len, 'MOND' as str FROM dual union all SELECT length('FRID') as len, 'FRID' as str FROM dual union all SELECT length('WESD') as len, 'WESD' as str FROM dual union all SELECT length('TUED') as len, 'TUED' as str FROM dual UNION ALL SELECT length('MWFST') as len, 'MWFST' as str FROM dual union all SELECT length('OERUU') as len, 'OERUU' as str FROM dual union all SELECT length('NSINE') as len, 'NSINE' as str FROM dual union all SELECT length('DDDDD') as len, 'DDDDD' as str FROM dual; COLUMN Rectangle FORMAT A12 COLUMN Blank_SUDOKU FORMAT A22 COLUMN Imposs_combo FORMAT A20 COLUMN Divisible_1_to_9 FORMAT A16 ---------------------Quadruplet Puzzle--------------------------- SELECT MAX(CASE WHEN id = chr(49) THEN str END ) AS Rectangle , MAX(CASE WHEN id = chr(50) THEN str END ) AS Blank_SUDOKU , MAX(CASE WHEN id = chr(51) THEN str END ) AS Imposs_combo, MAX(CASE WHEN id = chr(53) THEN str END ) AS Divisible_1_to_9 FROM ( SELECT regexp_replace(XMLAgg(XMLElement(X, word) order by rn), chr(60)||chr(88)||chr(62)||chr(124)||chr(60)||chr(47)||chr(88)||chr(62)||chr(60)||chr(88)||chr(62)||chr(124)|| chr(60)||chr(47)||chr(88)||chr(62) ) AS str , chr(49) as id FROM (SELECT word, rn FROM (SELECT word, rtrim(ltrim(path,chr(44)),chr(44)) path, ch_level, rect_size FROM(SELECT path, word, ch_level, word_len * cnt as rect_size, max(word_len * cnt) OVER ( ) max_rect FROM (SELECT path, word,len, word_len, count(*) over (partition by path) cnt, ch_level FROM (SELECT path,word,len, length(word) word_len ,ch_level, length(path)- length(regexp_replace (path, chr(44))) - chr(49) as num_chars FROM (SELECT path,len, str_level, ch_level, regexp_replace(sys_connect_by_path(ch,','),chr(44)) word FROM (SELECT path, len, str_level,substr(st,LEVEL, chr(49)) ch, LEVEL ch_level FROM ( WITH DATA AS (SELECT path,len, LEVEL str_level, substr(path, regexp_instr(path, chr(44), chr(49), LEVEL ) + chr(49), regexp_instr(path, chr(44), chr(49), LEVEL+ chr(49)) - regexp_instr(path, chr(44), chr(49), LEVEL) - chr(49)) st FROM (SELECT sys_connect_by_path(str,',')|| chr(44) path, len FROM (SELECT str,ch_3,len,xml_123,xml_145, xml_167 FROM (SELECT str as str, substr(str,chr(49),chr(51)) as ch_3, len from IOT3), (SELECT xmlserialize(content xmlagg(xmlelement(x,substr(str,chr(49),chr(51))) order by str)) xml_123, xmlserialize(content xmlagg(xmlelement(x,substr(str,chr(49),chr(49))||substr(str,chr(52),chr(50))) order by str)) xml_145, xmlserialize(content xmlagg(xmlelement(x,substr(str,chr(49),chr(49))||substr(str,chr(54),chr(50))) order by str)) xml_167 FROM IOT3) ) WHERE LEVEL >= chr(50) AND CONNECT_BY_ISLEAF = chr(49) CONNECT BY NOCYCLE PRIOR len = len AND PRIOR str != str AND LEVEL <= (SELECT max(length(str)) from IOT3) AND CASE WHEN LEVEL = chr(51) THEN CASE WHEN regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(49),chr(49))||PRIOR substr(str,chr(49),chr(49))||substr(str,chr(49),chr(49)))>chr(48) AND regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(50),chr(49))||PRIOR substr(str,chr(50),chr(49))||substr(str,chr(50),chr(49)))>chr(48) AND regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(51),chr(49))||PRIOR substr(str,chr(51),chr(49))||substr(str,chr(51),chr(49)))>chr(48) THEN chr(49) END ELSE chr(49) END = chr(49) AND CASE WHEN LEVEL = chr(51) THEN CASE len WHEN to_number(chr(52)) THEN CASE WHEN regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(52),chr(49))||PRIOR substr(str,chr(52),chr(49))||substr(str,chr(52),chr(49)))>chr(48) THEN chr(49) END WHEN to_number(chr(53)) THEN CASE WHEN regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(53),chr(49))||PRIOR substr(str,chr(53),chr(49))||substr(str,chr(53),chr(49)))>chr(48) THEN chr(49) END WHEN to_number(chr(54)) THEN CASE WHEN regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(54),chr(49))||PRIOR substr(str,chr(54),chr(49))||substr(str,chr(54),chr(49)))>chr(48) THEN chr(49) END WHEN to_number(chr(55)) THEN CASE WHEN regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(55),chr(49))||PRIOR substr(str,chr(55),chr(49))||substr(str,chr(55),chr(49)))>chr(48) THEN chr(49) END WHEN to_number(chr(56)) THEN CASE WHEN regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(56),chr(49))||PRIOR substr(str,chr(56),chr(49))||substr(str,chr(56),chr(49)))>chr(48) THEN chr(49) END ELSE chr(49) END ELSE chr(49) END = chr(49) AND CASE WHEN LEVEL = chr(53) THEN CASE WHEN regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(49),chr(49))||PRIOR substr(str,chr(49),chr(49))||substr(str,chr(49),chr(49)))>chr(48) AND regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(50),chr(49))||PRIOR substr(str,chr(50),chr(49))||substr(str,chr(50),chr(49)))>chr(48) AND regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(51),chr(49))||PRIOR substr(str,chr(51),chr(49))||substr(str,chr(51),chr(49)))>chr(48) THEN chr(49) END ELSE chr(49) END = chr(49) AND CASE WHEN LEVEL = chr(53) THEN CASE len WHEN to_number(chr(52)) THEN CASE WHEN regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(52),chr(49))||PRIOR substr(str,chr(52),chr(49))||substr(str,chr(52),chr(49)))>chr(48) THEN chr(49) END WHEN to_number(chr(53)) THEN CASE WHEN regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(53),chr(49))||PRIOR substr(str,chr(53),chr(49))||substr(str,chr(53),chr(49)))>chr(48) THEN chr(49) END WHEN to_number(chr(54)) THEN CASE WHEN regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(54),chr(49))||PRIOR substr(str,chr(54),chr(49))||substr(str,chr(54),chr(49)))>chr(48) THEN chr(49) END WHEN to_number(chr(55)) THEN CASE WHEN regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(55),chr(49))||PRIOR substr(str,chr(55),chr(49))||substr(str,chr(55),chr(49)))>chr(48) THEN chr(49) END WHEN to_number(chr(56)) THEN CASE WHEN regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(56),chr(49))||PRIOR substr(str,chr(56),chr(49))||substr(str,chr(56),chr(49)))>chr(48) THEN chr(49) END ELSE chr(49) END ELSE chr(49) END = chr(49) AND CASE WHEN LEVEL = chr(55) THEN CASE WHEN regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(49),chr(49))||PRIOR substr(str,chr(49),chr(49))||substr(str,chr(49),chr(49)))>chr(48) AND regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(50),chr(49))||PRIOR substr(str,chr(50),chr(49))||substr(str,chr(50),chr(49)))>chr(48) AND regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(51),chr(49))||PRIOR substr(str,chr(51),chr(49))||substr(str,chr(51),chr(49)))>chr(48) THEN chr(49) END ELSE chr(49) END = chr(49) AND CASE WHEN LEVEL = chr(55) THEN CASE len WHEN to_number(chr(52)) THEN CASE WHEN regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(52),chr(49))||PRIOR substr(str,chr(52),chr(49))||substr(str,chr(52),chr(49)))>chr(48) THEN chr(49) END WHEN to_number(chr(53)) THEN CASE WHEN regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(53),chr(49))||PRIOR substr(str,chr(53),chr(49))||substr(str,chr(53),chr(49)))>chr(48) THEN chr(49) END WHEN to_number(chr(54)) THEN CASE WHEN regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(54),chr(49))||PRIOR substr(str,chr(54),chr(49))||substr(str,chr(54),chr(49)))>chr(48) THEN chr(49) END WHEN to_number(chr(55)) THEN CASE WHEN regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(55),chr(49))||PRIOR substr(str,chr(55),chr(49))||substr(str,chr(55),chr(49)))>chr(48) THEN chr(49) END WHEN to_number(chr(56)) THEN CASE WHEN regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(56),chr(49))||PRIOR substr(str,chr(56),chr(49))||substr(str,chr(56),chr(49)))>chr(48) THEN chr(49) END ELSE chr(49) END ELSE chr(49) END = chr(49) ) CONNECT BY PRIOR path = path AND PRIOR len = len AND regexp_instr (path, chr(44), chr(49), LEVEL + chr(49)) > chr(48) AND PRIOR dbms_random.string (chr(112), SQRT(chr(52)||chr(48)||chr(48))) IS NOT NULL ) SELECT path, len, ST, str_level FROM DATA ) CONNECT BY PRIOR path = path AND PRIOR len = len AND PRIOR st = st AND lEVEL <= len AND PRIOR dbms_random.string (chr(112), floor(EXP(chr(51)))) IS NOT NULL ) WHERE CONNECT_BY_ISLEAF = chr(49) CONNECT BY PRIOR path = path AND CONNECT_BY_ROOT str_level = chr(49) AND PRIOR len = len AND PRIOR ch_level = ch_level AND PRIOR str_level + chr(49) = str_level AND PRIOR dbms_random.string(chr(112), POWER(chr(50), chr(52))) IS NOT NULL ) WHERE word IN (SELECT str FROM IOT3) ) WHERE num_chars = word_len ) WHERE cnt = len ) WHERE max_rect = rect_size ) MODEL DIMENSION BY (row_number() over (order by path, ch_level) as rn) MEASURES (path, rpad(chr(45), max(length(word)) over ( ) + chr(50), chr(45)) as line, utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(word))) as word, dense_rank( ) over (order by PATH) rank ) ( word[ANY] order by rn = CASE WHEN word[cv() - chr(49)] IS NULL THEN line[cv()]||chr(10)||chr(124)||word[cv()]||chr(124)||chr(10) WHEN word[cv() + chr(49) ] IS NULL THEN chr(124)||word[cv()]||chr(124)||chr(10)||line[cv()] ELSE CASE WHEN rank[cv()] != rank[cv() + chr(49)] THEN chr(124)||word[cv()]||chr(124) ||chr(10)||line[cv()]||chr(10) ELSE chr(124)||word[cv()]||chr(124)||chr(10) END END ) ) UNION ALL SELECT regexp_replace(XMLAgg(XMLElement(X, str||chr(10)) order by rn), chr(60)||chr(88)||chr(62)||chr(124)||chr(60)||chr(47)||chr(88)||chr(62)||chr(60)||chr(88)||chr(62)||chr(124)|| chr(60)||chr(47)||chr(88)||chr(62) ) AS str, chr(50) as id FROM ( SELECT ROWNUM rn, CASE WHEN ROWNUM = chr(49) THEN line|| chr(10)||chr(124)||substr(fin_str,chr(49),chr(51))||chr(32)||chr(124)||chr(32)||substr(fin_str,chr(52),chr(51)) ||chr(32)||chr(124)||chr(32)||substr(fin_str,chr(55),chr(51))||chr(124) WHEN ROWNUM IN (chr(51), chr(54), chr(57)) THEN chr(124)||substr(fin_str,chr(49),chr(51))||chr(32)||chr(124)||chr(32)||substr(fin_str,chr(52),chr(51)) ||chr(32)||chr(124)||chr(32)||substr(fin_str,chr(55),chr(51))||chr(124)||chr(10)||line ELSE chr(124)||substr(fin_str,chr(49),chr(51))||chr(32)||chr(124)||chr(32)||substr(fin_str,chr(52),chr(51)) ||chr(32)||chr(124)||chr(32)||substr(fin_str,chr(55),chr(51))||chr(124) END str FROM (SELECT substr(fin_str,(LEVEL - chr(49)) * chr(57) + chr(49), chr(57)) as fin_str, rpad(chr(45), chr(49)||chr(55), chr(45)) as line FROM (SELECT fin_str FROM (SELECT fin_str FROM dual MODEL DIMENSION BY (0 i ) MEASURES (CAST(NULL AS NUMBER) num, CAST(NULL AS NUMBER) tmp, CAST(NULL AS NUMBER) fin , CAST(NULL AS NUMBER) tmp_fin, CAST(NULL AS varchar2(4000)) str, CAST(NULL AS varchar2(4000)) fin_str) RULES ITERATE ( 81) (num[ITERATION_NUMBER] = chr(49), tmp_fin[ANY] = mod(nvl(fin[cv()- chr(49)], chr(48)), chr(57)) + chr(49), str[ANY] = str[cv()- chr(49)] || to_char(tmp_fin[cv()]), fin[ANY] = nvl(fin [cv()-chr(49)], chr(48)) + num[cv()] + nvl(tmp[cv()], chr(48)), tmp[ANY] = CASE WHEN cv(i)+ chr(49) IN (chr(57), chr(49)||chr(56), chr(51)||chr(54), chr(52)||chr(53),chr(54)||chr(51),chr(55)||chr(50)) THEN chr(51) WHEN cv(i)+chr(49) IN (chr(50)||chr(55),chr(53)||chr(52),chr(56)||chr(49)) THEN chr(52) END, fin_str[ANY] = CASE WHEN str[cv() + chr(49)] IS NULL THEN str[cv()] END ) ) WHERE fin_str IS NOT NULL ) CONNECT BY LEVEL CREATE TABLE IOT3 2 (len, str, 3 PRIMARY KEY (len, str ) 4 ) ORGANIZATION INDEX AS 5 SELECT length('SUND') as len, 'SUND' as str FROM dual 6 union all 7 SELECT length('MOND') as len, 'MOND' as str FROM dual 8 union all 9 SELECT length('FRID') as len, 'FRID' as str FROM dual 10 union all 11 SELECT length('WESD') as len, 'WESD' as str FROM dual 12 union all 13 SELECT length('TUED') as len, 'TUED' as str FROM dual 14 UNION ALL 15 SELECT length('MWFST') as len, 'MWFST' as str FROM dual 16 union all 17 SELECT length('OERUU') as len, 'OERUU' as str FROM dual 18 union all 19 SELECT length('NSINE') as len, 'NSINE' as str FROM dual 20 union all 21 SELECT length('DDDDD') as len, 'DDDDD' as str FROM dual; Table created. SQL> COLUMN Rectangle FORMAT A12 SQL> COLUMN Blank_SUDOKU FORMAT A22 SQL> COLUMN Imposs_combo FORMAT A20 SQL> COLUMN Divisible_1_to_9 FORMAT A16 1 SELECT 2 MAX(CASE WHEN id = chr(49) THEN str END ) AS Rectangle , 3 MAX(CASE WHEN id = chr(50) THEN str END ) AS Blank_SUDOKU , 4 MAX(CASE WHEN id = chr(51) THEN str END ) AS Imposs_combo, 5 MAX(CASE WHEN id = chr(53) THEN str END ) AS Divisible_1_to_9 6 FROM 7 ( 8 SELECT regexp_replace(XMLAgg(XMLElement(X, word) order by rn), 9 chr(60)||chr(88)||chr(62)||chr(124)||chr(60)||chr(47)||chr(88)||chr(62)||chr(60)||chr(88)||chr(62)||chr(124)|| chr(60)||chr(47)||chr(88)||chr(62) 10 ) AS str , chr(49) as id 11 FROM 12 (SELECT word, rn 13 FROM 14 (SELECT word, rtrim(ltrim(path,chr(44)),chr(44)) path, ch_level, rect_size 15 FROM(SELECT path, word, ch_level, word_len * cnt as rect_size, 16 max(word_len * cnt) OVER ( ) max_rect 17 FROM (SELECT path, word,len, word_len, count(*) over (partition by path) cnt, ch_level 18 FROM (SELECT path,word,len, length(word) word_len ,ch_level, 19 length(path)- length(regexp_replace (path, chr(44))) - chr(49) as num_chars 20 FROM (SELECT path,len, str_level, ch_level, 21 regexp_replace(sys_connect_by_path(ch,','),chr(44)) word 22 FROM (SELECT path, len, str_level,substr(st,LEVEL, chr(49)) ch, LEVEL ch_level 23 FROM ( 24 WITH DATA AS 25 (SELECT path,len, LEVEL str_level, 26 substr(path, 27 regexp_instr(path, chr(44), chr(49), LEVEL ) + chr(49), 28 regexp_instr(path, chr(44), chr(49), LEVEL+ chr(49)) - 29 regexp_instr(path, chr(44), chr(49), LEVEL) - chr(49)) st 30 FROM 31 (SELECT sys_connect_by_path(str,',')|| chr(44) path, len 32 FROM 33 (SELECT str,ch_3,len,xml_123,xml_145, xml_167 34 FROM 35 (SELECT str as str, substr(str,chr(49),chr(51)) as ch_3, len from IOT3), 36 (SELECT xmlserialize(content xmlagg(xmlelement(x,substr(str,chr(49),chr(51))) order by str)) xml_123, 37 xmlserialize(content xmlagg(xmlelement(x,substr(str,chr(49),chr(49))||substr(str,chr(52),chr(50))) order by str)) xml_145, 38 xmlserialize(content xmlagg(xmlelement(x,substr(str,chr(49),chr(49))||substr(str,chr(54),chr(50))) order by str)) xml_167 39 FROM IOT3) 40 ) 41 WHERE LEVEL >= chr(50) 42 AND CONNECT_BY_ISLEAF = chr(49) 43 CONNECT BY NOCYCLE PRIOR len = len 44 AND PRIOR str != str 45 AND LEVEL <= (SELECT max(length(str)) from IOT3) 46 AND CASE WHEN LEVEL = chr(51) 47 THEN CASE WHEN regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(49),chr(49))||PRIOR substr(str,chr(49),chr(49))||substr(str,chr(49),chr(49)))>chr(48) 48 AND regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(50),chr(49))||PRIOR substr(str,chr(50),chr(49))||substr(str,chr(50),chr(49)))>chr(48) 49 AND regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(51),chr(49))||PRIOR substr(str,chr(51),chr(49))||substr(str,chr(51),chr(49)))>chr(48) 50 THEN chr(49) 51 END 52 ELSE chr(49) END = chr(49) 53 AND CASE WHEN LEVEL = chr(51) 54 THEN CASE len 55 WHEN to_number(chr(52)) 56 THEN CASE WHEN regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(52),chr(49))||PRIOR substr(str,chr(52),chr(49))||substr(str,chr(52),chr(49)))>chr(48) 57 THEN chr(49) 58 END 59 WHEN to_number(chr(53)) 60 THEN CASE WHEN regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(53),chr(49))||PRIOR substr(str,chr(53),chr(49))||substr(str,chr(53),chr(49)))>chr(48) 61 THEN chr(49) 62 END 63 WHEN to_number(chr(54)) 64 THEN CASE WHEN regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(54),chr(49))||PRIOR substr(str,chr(54),chr(49))||substr(str,chr(54),chr(49)))>chr(48) 65 THEN chr(49) 66 END 67 WHEN to_number(chr(55)) 68 THEN CASE WHEN regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(55),chr(49))||PRIOR substr(str,chr(55),chr(49))||substr(str,chr(55),chr(49)))>chr(48) 69 THEN chr(49) 70 END 71 WHEN to_number(chr(56)) 72 THEN CASE WHEN regexp_instr(xml_123,CONNECT_BY_ROOT substr(str,chr(56),chr(49))||PRIOR substr(str,chr(56),chr(49))||substr(str,chr(56),chr(49)))>chr(48) 73 THEN chr(49) 74 END 75 ELSE chr(49) 76 END 77 ELSE chr(49) END = chr(49) 78 AND CASE WHEN LEVEL = chr(53) 79 THEN CASE WHEN regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(49),chr(49))||PRIOR substr(str,chr(49),chr(49))||substr(str,chr(49),chr(49)))>chr(48) 80 AND regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(50),chr(49))||PRIOR substr(str,chr(50),chr(49))||substr(str,chr(50),chr(49)))>chr(48) 81 AND regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(51),chr(49))||PRIOR substr(str,chr(51),chr(49))||substr(str,chr(51),chr(49)))>chr(48) 82 THEN chr(49) 83 END 84 ELSE chr(49) END = chr(49) 85 AND CASE WHEN LEVEL = chr(53) 86 THEN CASE len 87 WHEN to_number(chr(52)) 88 THEN CASE WHEN regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(52),chr(49))||PRIOR substr(str,chr(52),chr(49))||substr(str,chr(52),chr(49)))>chr(48) 89 THEN chr(49) 90 END 91 WHEN to_number(chr(53)) 92 THEN CASE WHEN regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(53),chr(49))||PRIOR substr(str,chr(53),chr(49))||substr(str,chr(53),chr(49)))>chr(48) 93 THEN chr(49) 94 END 95 WHEN to_number(chr(54)) 96 THEN CASE WHEN regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(54),chr(49))||PRIOR substr(str,chr(54),chr(49))||substr(str,chr(54),chr(49)))>chr(48) 97 THEN chr(49) 98 END 99 WHEN to_number(chr(55)) 100 THEN CASE WHEN regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(55),chr(49))||PRIOR substr(str,chr(55),chr(49))||substr(str,chr(55),chr(49)))>chr(48) 101 THEN chr(49) 102 END 103 WHEN to_number(chr(56)) 104 THEN CASE WHEN regexp_instr(xml_145,CONNECT_BY_ROOT substr(str,chr(56),chr(49))||PRIOR substr(str,chr(56),chr(49))||substr(str,chr(56),chr(49)))>chr(48) 105 THEN chr(49) 106 END 107 ELSE chr(49) 108 END 109 ELSE chr(49) END = chr(49) 110 AND CASE WHEN LEVEL = chr(55) 111 THEN CASE WHEN regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(49),chr(49))||PRIOR substr(str,chr(49),chr(49))||substr(str,chr(49),chr(49)))>chr(48) 112 AND regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(50),chr(49))||PRIOR substr(str,chr(50),chr(49))||substr(str,chr(50),chr(49)))>chr(48) 113 AND regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(51),chr(49))||PRIOR substr(str,chr(51),chr(49))||substr(str,chr(51),chr(49)))>chr(48) 114 THEN chr(49) 115 END 116 ELSE chr(49) END = chr(49) 117 AND CASE WHEN LEVEL = chr(55) 118 THEN CASE len 119 WHEN to_number(chr(52)) 120 THEN CASE WHEN regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(52),chr(49))||PRIOR substr(str,chr(52),chr(49))||substr(str,chr(52),chr(49)))>chr(48) 121 THEN chr(49) 122 END 123 WHEN to_number(chr(53)) 124 THEN CASE WHEN regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(53),chr(49))||PRIOR substr(str,chr(53),chr(49))||substr(str,chr(53),chr(49)))>chr(48) 125 THEN chr(49) 126 END 127 WHEN to_number(chr(54)) 128 THEN CASE WHEN regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(54),chr(49))||PRIOR substr(str,chr(54),chr(49))||substr(str,chr(54),chr(49)))>chr(48) 129 THEN chr(49) 130 END 131 WHEN to_number(chr(55)) 132 THEN CASE WHEN regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(55),chr(49))||PRIOR substr(str,chr(55),chr(49))||substr(str,chr(55),chr(49)))>chr(48) 133 THEN chr(49) 134 END 135 WHEN to_number(chr(56)) 136 THEN CASE WHEN regexp_instr(xml_167,CONNECT_BY_ROOT substr(str,chr(56),chr(49))||PRIOR substr(str,chr(56),chr(49))||substr(str,chr(56),chr(49)))>chr(48) 137 THEN chr(49) 138 END 139 ELSE chr(49) 140 END 141 ELSE chr(49) END = chr(49) 142 ) 143 CONNECT BY PRIOR path = path 144 AND PRIOR len = len 145 AND regexp_instr (path, chr(44), chr(49), LEVEL + chr(49)) > chr(48) 146 AND PRIOR dbms_random.string (chr(112), SQRT(chr(52)||chr(48)||chr(48))) IS NOT NULL 147 ) 148 SELECT path, len, ST, str_level FROM DATA 149 ) 150 CONNECT BY PRIOR path = path 151 AND PRIOR len = len 152 AND PRIOR st = st 153 AND lEVEL <= len 154 AND PRIOR dbms_random.string (chr(112), floor(EXP(chr(51)))) IS NOT NULL 155 ) 156 WHERE CONNECT_BY_ISLEAF = chr(49) 157 CONNECT BY PRIOR path = path 158 AND CONNECT_BY_ROOT str_level = chr(49) 159 AND PRIOR len = len 160 AND PRIOR ch_level = ch_level 161 AND PRIOR str_level + chr(49) = str_level 162 AND PRIOR dbms_random.string(chr(112), POWER(chr(50), chr(52))) IS NOT NULL 163 ) 164 WHERE word IN (SELECT str FROM IOT3) 165 ) 166 WHERE num_chars = word_len 167 ) 168 WHERE cnt = len 169 ) 170 WHERE max_rect = rect_size 171 ) 172 MODEL 173 DIMENSION BY (row_number() over (order by path, ch_level) as rn) 174 MEASURES (path, rpad(chr(45), max(length(word)) over ( ) + chr(50), chr(45)) as line, 175 utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(word))) as word, 176 dense_rank( ) over (order by PATH) rank 177 ) 178 ( 179 word[ANY] order by rn = 180 CASE WHEN word[cv() - chr(49)] IS NULL 181 THEN line[cv()]||chr(10)||chr(124)||word[cv()]||chr(124)||chr(10) 182 WHEN word[cv() + chr(49) ] IS NULL 183 THEN chr(124)||word[cv()]||chr(124)||chr(10)||line[cv()] 184 ELSE CASE WHEN rank[cv()] != rank[cv() + chr(49)] 185 THEN chr(124)||word[cv()]||chr(124) ||chr(10)||line[cv()]||chr(10) 186 ELSE chr(124)||word[cv()]||chr(124)||chr(10) END 187 END 188 ) 189 ) 190 UNION ALL 191 SELECT regexp_replace(XMLAgg(XMLElement(X, str||chr(10)) order by rn), chr(60)||chr(88)||chr(62)||chr(124)||chr(60)||chr(47)||chr(88)||chr(62)||chr(60)||chr(88)||chr(62)||chr(124)|| chr(60)||chr(47)||chr(88)||chr(62) 192 ) AS str, chr(50) as id 193 FROM 194 ( 195 SELECT ROWNUM rn, 196 CASE WHEN ROWNUM = chr(49) 197 THEN line|| chr(10)||chr(124)||substr(fin_str,chr(49),chr(51))||chr(32)||chr(124)||chr(32)||substr(fin_str,chr(52),chr(51)) 198 ||chr(32)||chr(124)||chr(32)||substr(fin_str,chr(55),chr(51))||chr(124) 199 WHEN ROWNUM IN (chr(51), chr(54), chr(57)) 200 THEN chr(124)||substr(fin_str,chr(49),chr(51))||chr(32)||chr(124)||chr(32)||substr(fin_str,chr(52),chr(51)) 201 ||chr(32)||chr(124)||chr(32)||substr(fin_str,chr(55),chr(51))||chr(124)||chr(10)||line 202 ELSE chr(124)||substr(fin_str,chr(49),chr(51))||chr(32)||chr(124)||chr(32)||substr(fin_str,chr(52),chr(51)) 203 ||chr(32)||chr(124)||chr(32)||substr(fin_str,chr(55),chr(51))||chr(124) 204 END str 205 FROM 206 (SELECT substr(fin_str,(LEVEL - chr(49)) * chr(57) + chr(49), chr(57)) as fin_str, rpad(chr(45), 207 chr(49)||chr(55), chr(45)) as line 208 FROM 209 (SELECT fin_str 210 FROM 211 (SELECT fin_str FROM dual 212 MODEL 213 DIMENSION BY (0 i ) 214 MEASURES (CAST(NULL AS NUMBER) num, CAST(NULL AS NUMBER) tmp, 215 CAST(NULL AS NUMBER) fin , CAST(NULL AS NUMBER) tmp_fin, 216 CAST(NULL AS varchar2(4000)) str, 217 CAST(NULL AS varchar2(4000)) fin_str) 218 RULES ITERATE ( 81) 219 (num[ITERATION_NUMBER] = chr(49), 220 tmp_fin[ANY] = mod(nvl(fin[cv()- chr(49)], chr(48)), chr(57)) + chr(49), 221 str[ANY] = str[cv()- chr(49)] || to_char(tmp_fin[cv()]), 222 fin[ANY] = nvl(fin [cv()-chr(49)], chr(48)) + num[cv()] + nvl(tmp[cv()], chr(48)), 223 tmp[ANY] = CASE WHEN cv(i)+ chr(49) IN (chr(57), chr(49)||chr(56), chr(51)||chr(54), chr(52)||chr(53),chr(54)||chr(51),chr(55)||chr(50)) 224 THEN chr(51) 225 WHEN cv(i)+chr(49) IN (chr(50)||chr(55),chr(53)||chr(52),chr(56)||chr(49)) 226 THEN chr(52) END, 227 fin_str[ANY] = CASE WHEN str[cv() + chr(49)] IS NULL 228 THEN str[cv()] END 229 ) 230 ) 231 WHERE fin_str IS NOT NULL 232 ) 233 CONNECT BY LEVEL