declare 
  table_or_view_does_not_exist exception; 
  pragma exception_init(table_or_view_does_not_exist, -00942); 
  object_does_not_exist exception; 
  pragma exception_init(object_does_not_exist, -04043); 
  sequence_does_not_exist exception; 
  pragma exception_init(sequence_does_not_exist, -02289); 
begin 
  execute immediate q'[drop table t_alphabet purge]'; 
exception 
  when table_or_view_does_not_exist 
    or object_does_not_exist 
    or sequence_does_not_exist then null; -- on purpose, hide any error 
end; 

create table t_alphabet 
(thecount number 
,alphabet varchar2(4000) 
) 

begin 
  insert into t_alphabet(alphabet, thecount) values ('Alfa;Bravo;Charlie;Delta;Echo;Foxtrot;Golf',7); 
  insert into t_alphabet(alphabet, thecount) values ('Hotel;India;Juliett;Kilo;Lima;Mike;November',7); 
  insert into t_alphabet(alphabet, thecount) values ('Oscar;Papa;Quebec;Romeo;Sierra;Tango;Uniform',7); 
  insert into t_alphabet(alphabet, thecount) values ('Victor;Whiskey;;X-ray;Yankee;;Zulu',5); 
  insert into t_alphabet(alphabet, thecount) values ('Alfa.Bravo.Charlie.Delta.Echo.Foxtrot.Golf',7); 
  insert into t_alphabet(alphabet, thecount) values ('Hotel.India.Juliett.Kilo.Lima.Mike.November',7); 
  insert into t_alphabet(alphabet, thecount) values ('Oscar.Papa.Quebec.Romeo.Sierra.Tango.Uniform',7); 
  insert into t_alphabet(alphabet, thecount) values ('Victor.Whiskey..X-ray.Yankee..Zulu',5); 
  commit; 
end; 

create or replace package separated_ptf is 
  function describe(tab        in out dbms_tf.table_t 
                   ,cols       in dbms_tf.columns_t default null 
                   ,coltosplit in varchar2 default null) return dbms_tf.describe_t; 
 
  procedure fetch_rows(coltosplit in varchar2 default null); 
end separated_ptf; 

create or replace package body separated_ptf as 
  function describe(tab        in out dbms_tf.table_t 
                   ,cols       in dbms_tf.columns_t default null 
                   ,coltosplit in varchar2 default null) return dbms_tf.describe_t as 
    -- metadata for column to add 
    l_new_col dbms_tf.column_metadata_t; 
    -- table of columns to add 
    l_new_cols dbms_tf.columns_new_t; -- := DBMS_TF.COLUMNS_NEW_T(); 
    -- make sure the column to split is in the correct format (uppercase with doublequotes) 
    l_coltosplit dbms_quoted_id := dbms_assert.enquote_name(str => coltosplit, capitalize => true); 
  begin 
    -- if the coltosplit parameter is null then 
    if coltosplit is null then 
      -- Mark the first column ReadOnly and don't display it anymore 
      tab.column(1).for_read := true; 
      tab.column(1).pass_through := false; 
    else 
      -- if the coltosplit parameter is not null then 
      -- check every column from the source table 
      for indx in tab.column.first .. tab.column.last loop 
        -- if this is the column we want to split then 
        if tab.column(indx).description.name = l_coltosplit then 
          -- Mark this column ReadOnly and don't display it anymore 
          tab.column(indx).for_read := true; 
          tab.column(indx).pass_through := false; 
        end if; 
      end loop; 
    end if; 
    -- Add the new columns, as specified in the cols parameter 
    for indx in 1 .. cols.count loop 
      -- define metadata for column named cols(indx) 
      -- that will default to a datatype of varchar2 with 
      -- a length of 4000 
      l_new_col := dbms_tf.column_metadata_t(name => cols(indx)); 
      -- add the new column to the list of columns new columns 
      l_new_cols(l_new_cols.count + 1) := l_new_col; 
    end loop; 
    -- Instead of returning NULL we will RETURN a specific 
    -- DESCRIBE_T that adds new columns 
    return dbms_tf.describe_t(new_columns => l_new_cols); 
  end; 
 
  procedure fetch_rows(coltosplit in varchar2 default null) is 
    -- define a table type of varchar2 tables 
    type colset is table of dbms_tf.tab_varchar2_t index by pls_integer; 
    -- variable to hold the rowset as retrieved 
    l_rowset dbms_tf.row_set_t; 
    -- variable to hold the number of rows as retrieved 
    l_rowcount pls_integer; 
    -- variable to hold the number of put columns 
    l_putcolcount pls_integer := dbms_tf.get_env().put_columns.count; 
    -- variable to hold the new values 
    l_newcolset colset; 
    -- get the name of the column to be split from the get columns 
    l_coltosplit dbms_quoted_id := trim('"' from dbms_tf.get_env().get_columns(1).name); 
  begin 
    --    dbms_tf.Trace(dbms_tf.Get_Env); 
    --    dbms_output.put_line(q'[l_coltosplit : ]'||l_coltosplit); 
    -- fetch rows into a local rowset 
    -- at this point the rows will have columns 
    -- from the the table/view/query passed in 
    dbms_tf.get_row_set(l_rowset, l_rowcount); 
    -- for every row in the rowset... 
    for rowindx in 1 .. l_rowcount loop 
      -- for every column 
      for colindx in 1 .. l_putcolcount loop 
        -- split the row into separate values 
        --  FUNCTION Row_To_Char(rowset Row_Set_t, 
        --                       rid    PLS_INTEGER, 
        --                       format PLS_INTEGER default FORMAT_JSON) 
        --           return VARCHAR2; 
        -- splitting the regexp way: http://nuijten.blogspot.com/2009/07/splitting-comma-delimited-string-regexp.html 
--        l_newcolset(colindx)(rowindx) := trim(separator from regexp_substr(json_value(dbms_tf.row_to_char(l_rowset, rowindx), '$.' || l_coltosplit) 
--                                                           ,'[^' || separator || ']*' || separator || '{0,1}' 
--                                                           ,1 
--                                                           ,colindx)); 
        l_newcolset(colindx)(rowindx) := trim(';' from regexp_substr(json_value(dbms_tf.row_to_char(l_rowset, rowindx), '$.' || l_coltosplit) 
                                                           ,'[^;]*;{0,1}' 
                                                           ,1 
                                                           ,colindx)); 
      end loop; -- every column 
    end loop; -- every row in the rowset 
    -- add the newly populated columns to the rowset 
    for indx in 1 .. l_putcolcount loop 
      dbms_tf.put_col(columnid => indx, collection => l_newcolset(indx)); 
    end loop; 
  end; 
end separated_ptf; 

create or replace function separated_fnc(p_tbl      in table 
                                        ,cols       columns default null 
                                        ,coltosplit in varchar2 default null) return table 
  pipelined row polymorphic using separated_ptf; 

select * 
  from separated_fnc(t_alphabet, columns(first, second, third, fourth, fifth, sixth, seventh)) 

select * 
  from separated_fnc(t_alphabet, columns(first, second, third, fourth, fifth, sixth, seventh),'alphabet')