demo@ORA11G> variable x varchar2(20)
demo@ORA11G> exec :x :='a,b,c,d,e';
PL/SQL procedure successfully completed.
demo@ORA11G> select instr(:x,',',1,level) pos
2 from dual
3 connect by level <= length(:x) -
4 length( replace(:x,','))+1 ;
POS
----------
2
4
6
8
0
5 rows selected.
demo@ORA11G> select :x input_txt , pos+1 start_pos,
2 lead(pos,1,4000) over(order by pos) - pos-1 end_pos
3 from (
4 select instr(:x,',',1,level) pos
5 from dual
6 connect by level <= length(:x) -
7 length( replace(:x,','))+1
8 ) ;
INPUT_TXT START_POS END_POS
-------------------------------- ---------- ----------
a,b,c,d,e 1 1
a,b,c,d,e 3 1
a,b,c,d,e 5 1
a,b,c,d,e 7 1
a,b,c,d,e 9 3991
5 rows selected.
demo@ORA11G> select :x input_txt , pos+1 start_pos,
2 lead(pos,1,4000) over(order by pos) - pos-1 end_pos
3 from (
4 select instr(:x,',',1,level) pos
5 from dual
6 connect by level <= length(:x) -
7 length( replace(:x,','))+1
8 order by pos
9 ) ;
INPUT_TXT START_POS END_POS
-------------------------------- ---------- ----------
a,b,c,d,e 1 1
a,b,c,d,e 3 1
a,b,c,d,e 5 1
a,b,c,d,e 7 1
a,b,c,d,e 9 3991
5 rows selected.
demo@ORA11G> select substr(:x , pos+1 ,
2 lead(pos,1,4000) over(order by pos) - pos-1) final_value
3 from (
4 select instr(:x,',',1,level) pos
5 from dual
6 connect by level <= length(:x) -
7 length( replace(:x,','))+1
8 order by pos
9 ) ;
FINAL_VALUE
--------------------------------
a
b
c
d
e
5 rows selected.
demo@ORA11G>
Without the "Order by" in subquery, the rows come out in random order, and it won't be deterministic.
Don't we need an "Order by" in In-line sql's? to make the result set to be deterministic, while passing to outer layers. please confirm.