String Split - Arrays

Example 1

DECLARE
  l_array           apex_t_varchar2;
BEGIN 
  l_array := apex_string.split(p_str => :PNN_DATA, p_sep  => ':');
  package_api.function_name(
      p_parameter_1 => l_array(1)     
     ,p_parameter_2 => l_array(2)
  );
END ; 

Example 2

with t as
(
  select 'ABC:123;XYZ:789' as col from dual union all
  select 'ABCD:1234;WXYZ:6789' as col from dual
)
, t2 as
(
  select t0.column_value as r
  from t
  cross join table(apex_string.split(t.col, ';')) t0
)
select t2.r
,      trim( ':' from regexp_substr( t2.r, '^.*:' ) ) as value_1
,      trim( ':' from regexp_substr( t2.r, ':.*$' ) ) as value_2
from t2
order by 1
/

Result set