create pipelined table function (PTF)

create or replace function split_string  
 (p_str IN VARCHAR2  
 ,p_delimiter IN VARCHAR2 default ','  
 ) RETURN sys.odcivarchar2list PIPELINED   
IS  
/** Function to split strings based upon delimiter  
*  
* @author Sven Weller  
*  
* @param p_str input string   
* @param p_delimiter delimiter string, default =' Delimiter should only be 1 char.  
* @return list of strings  
*  
*/  
 v_entry varchar2(4000);  
 v_remaining_str varchar2(4000);  
BEGIN  
  -- input string needs to hold something to be able to split  
  if p_str is not null then  
    <<steps>>  
    for i in 1..regexp_count(p_str,'\'||p_delimiter)+1 loop  
      -- search + split  
      v_entry := rtrim(regexp_substr(p_str,'[^\'||p_delimiter||']*('||p_delimiter||'|$)',1,i),p_delimiter);  
      pipe row(v_entry);  
    end loop steps;   
  else raise no_data_found;   
  end if;   
   
END split_string; 

use the TABLE operator

select * from TABLE(split_string('A:BB::CCC',':'))

12.2 without the TABLE operator

select * from split_string('A:BB::CCC',':')