Oracle APEX apex_string.split order by

user_1698822 0
Viewed 651

Does apex_string.split always guarantee that the order of the rows returned is the order of the characters of the string ? Can I rely on the rownum to always correspond to 1 for the first character of the split string ? or do I need to add a order by rownum ?

What is the method to get the rows in the same order of the characters of the string ?

My requirement is to insert the rows returned by apex_string.split in the same order as the characters of the string.

I am currently executing the below, will this maintain the character order ?

select t.column_value value, rownum seq
  from table(apex_string.split('test','')) t
bulk collect into ins_arr;

for i in ins_arr.first..ins_arr.last
loop

   /* execute insert statement */
   insert into table (seq, value ) 
   values (ins_arr.seq,ins_arr.value);

end loop

The insert should result in

seq value
1 t
2 e
3 s
4 t

Thank you in advance,

oracle-apex oracle
1 Answers

I don't think it's guaranteed, becuase if it was, it would be in the documentation. But I think you can accomplish what you want by changing your routine. (Note, I have not verified this.)

insert into table (seq, value)
select t.column_value value, 
       row_number() over (order by t.column_value)
from table(apex_string.split('test','')) t

I think you can do the same with rownum, but I'm never 100% sure what order the rownum and the order by happen in.

user_236690
Related