SQL> create table emp_names(first_name varchar2(100),last_name varchar2(100));

Table created.

SQL> create table emp_staging_tab(lob_col CLOB);

Table created.

SQL> insert into emp_staging_tab values('Chris,Saxon,Mohammed,Imran,Jim,Bunker,Saurav,Ganguly');

1 row created.

SQL> commit;

Commit complete.

SQL> set timing on
SQL> declare
  2    c clob;
  3    c1 varchar2(32767);
  4    tot int := 0;
  5  begin
  6    c1 := 'Chris,Saxon,Mohammed,Imran,Jim,Bunker,Saurav,Ganguly,' ||
  7           'Chris2,Saxon2,Mohammed2,Imran2,Jim2,Bunker2,Saurav2,Ganguly2,' ||
  8           'Chris3,Saxon3,Mohammed3,Imran3,Jim3,Bunker3,Saurav3,Ganguly3,';
  9
 10    c1 := c1 || c1 || c1 || c1 || c1 || c1 || c1 ;
 11
 12    for i in 1 .. 800 loop
 13      dbms_lob.createtemporary(c,true);
 14
 15      for j in 1 .. 40 loop
 16        dbms_lob.writeappend(c, length(c1), c1);
 17        tot := tot + 84;  -- first/second names in 'c1'
 18      end loop;
 19
 20      insert into emp_staging_tab values (c);
 21      commit;
 22
 23      dbms_lob.freetemporary(c);
 24    end loop;
 25  end;
 26  /

PL/SQL procedure successfully completed.

SQL> set serverout on
SQL> declare
  2    l_big_chunk varchar2(32767);
  3    l_leftover varchar2(200);
  4    l_offset pls_integer;
  5    l_len pls_integer;
  6
  7    type t_rows is table of emp_names%rowtype index by pls_integer;
  8    l_rows t_rows;
  9
 10    l_comma pls_integer;
 11    l_first_name boolean;
 12  begin
 13  for i in ( select rownum r, lob_col from emp_staging_tab ) loop
 14    l_offset := 1;
 15    l_first_name := true;
 16
 17    loop
 18      l_big_chunk := l_leftover || dbms_lob.substr(i.lob_col,32767,l_offset);
 19      l_len := length(l_big_chunk);
 20      l_offset := l_offset + l_len;
 21
 22      dbms_application_info.set_client_info(i.r||'-'||l_offset);
 23
 24      loop
 25        l_comma := instr(l_big_chunk,',');
 26        exit when l_comma = 0 or l_big_chunk is null;
 27        if l_comma != 0 then
 28           if l_first_name then
 29             l_rows(l_rows.count+1).first_name := substr(l_big_chunk,1,l_comma-1);
 30           else
 31             l_rows(l_rows.count).last_name := substr(l_big_chunk,1,l_comma-1);
 32           end if;
 33           l_first_name := not l_first_name;
 34           l_big_chunk := substr(l_big_chunk,l_comma+1);
 35        else
 36           l_leftover := l_big_chunk;
 37        end if;
 38      end loop;
 39
 40      exit when l_len < 32767;
 41    end loop;
 42
 43  end loop;
 44    forall i in 1 .. l_rows.count
 45       insert into emp_names values l_rows(i);
 46
 47    dbms_output.put_line(sql%rowcount);
 48    commit;
 49
 50  end;
 51  /
2688004

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.89

SQL> select * from emp_names
  2  where rownum < 100;

FIRST_NAME                     LAST_NAME
------------------------------ ----------------
Mohammed2                      Imran2
Jim2                           Bunker2
Saurav2                        Ganguly2
Chris3                         Saxon3
Mohammed3                      Imran3
...
...
...