参考了网上的代码修改实现，修改后可以按顺序获取数组

with str as (select 'first field,second field,third field,' as text,',' as split_ch from dual where rownum = 1)
select case
         when text = ' ' then
          null
         else
          replace(text,'  ',' ')
       end text,indext
  from (SELECT REGEXP_SUBSTR(replace(replace(str.text,' ','  '),str.split_ch || str.split_ch,str.split_ch || ' ' || str.split_ch),'[^' || str.split_ch || ']+',1,rownum) AS TEXT,ROWNUM AS INDEXT
          FROM dual,str
        CONNECT BY ROWNUM <= LENGTH(str.text) -
                   LENGTH(REPLACE(str.text,str.split_ch,'')) + 1)
 ORDER BY INDEXT