If you need to break up a delimited string into parts then you can use DBMS_UTILITY.comma_to_table. This function
breaks up a comma delimited string but if your string is delimited by another character you can simply replace it with
commas before passing to the function.

In the example shown below you will see the string (l_list) have its delimiter replaced with commas and the items wrapped in double speech marks. If you don't wrap the items they can not include numbers, spaces or reserved words.

DECLARE



  -- Declare variables

  l_list      VARCHAR2(100);

  s_delimiter VARCHAR2(1);

  l_tablen    BINARY_INTEGER;

  l_tab       DBMS_UTILITY.uncl_array;



BEGIN



  -- Define your delimiter and your list

  s_delimiter := ':';

  l_list := 'test1:test2';

 

  -- Swap delimiter for commas

  l_list := replace(l_list,s_delimiter,',');

  

  -- Enclose list items in double speech marks

  -- This allows the passing of number, reserved words

  -- and items with spaces in them

  l_list := '"' || replace(strValues,',','","') || '"';



  -- Convert comma separated list to table

  DBMS_UTILITY.comma_to_table (

    list   => l_list,

    tablen => l_tablen,

    tab    => l_tab);

    

  -- Items now in l_tab

  -- Number of items equal to l_tablen  

    

  -- Loop through each list item 

  FOR i IN 1 .. l_tablen LOOP

    

    -- Strip double speech marks as

    -- not required now

    l_tab(i) := replace(l_tab(i),'"','');

    

    -- Output each list item

    DBMS_OUTPUT.put_line('Item ' || i || ' : ' || l_tab(i));

    

  END LOOP;      

    

END;