StringTokenizer in Pl/Sql

Did you expect me to provide a function, a package or similars? 
...nope, as usual I provide the most elegant solution, this time is in an Object Oriented way
I'm using 

 

Object header

create or replace type StringTokenizer as object (
  s varchar2(32767),
  separator varchar2(32767),
  lastSepPosition number,
  CONSTRUCTOR FUNCTION StringTokenizer(s varchar2, separator varchar2)
    RETURN SELF AS RESULT,
  member function privNextToken(self in out StringTokenizer) return varchar2,
  member function nextToken(self in out StringTokenizer) return varchar2,
  member function hasNext(self in out StringTokenizer) return boolean
) 
INSTANTIABLE FINAL

Object body

create or replace type BODY StringTokenizer as 
  CONSTRUCTOR FUNCTION StringTokenizer(s varchar2, separator varchar2)
    RETURN SELF AS RESULT
  AS
  BEGIN
    SELF.s := s;
    self.separator := separator;
    self.lastSepPosition := 1;
    RETURN;
  END;
  --
  member function privNextToken(self in out StringTokenizer) return varchar2 is
    result varchar2(32767);
    sepPosition number;
  begin
    --dbms_output.put_line('instr(''' || s || ''', ''' || separator || ''',''' || lastSepPosition || ''')');
    sepPosition := instr(s, separator, lastSepPosition);
    if(sepPosition = 0) then
      sepPosition := length(s)+1;
    end if;
    --dbms_output.put_line(sepPosition);
    --dbms_output.put_line('substr(''' || s || ''', ''' || lastSepPosition || ''', ''' || (sepPosition-lastSepPosition) || ''')');
    result := substr(s, lastSepPosition, sepPosition-lastSepPosition);
    lastSepPosition := sepPosition+length(separator);
    return result;
  end;
  --
  member function nextToken(self in out StringTokenizer) return varchar2 is
    result varchar2(32767) := null;
  begin
    while(hasNext) loop
      result := privNextToken();
      if(not result is null and length(result)>0) then
        exit;
      end if;
    end loop;
    --dbms_output.put_line('returning ' || result || '*');
    return result;
  end;
  --
  member function hasNext(self in out StringTokenizer) return boolean is
  begin
    return lastSepPosition<length(s);
  end;
end;

Test

declare
  o StringTokenizer;
begin
  o := StringTokenizer('pippo@@pluto@@paperino', '@@');
  while(o.hasNext) loop
    dbms_output.put_line(o.nextTOken);
  end loop;
  dbms_output.put_line('***end***');
end;
/

-----------

pippo
pluto
paperino
***end***

 

A real tricky example
I want to extract the host performance information(iostat) and store in a table,
this example may not fit the same output generated from your operating system, so plz review and adapt
See Java tunnel Oracle-OS for how to access the OS from the database

CALL dbms_java.set_output(50000);

declare
  s varchar2(32767);
  st alworkshop.StringTokenizer;
  idleFound boolean := false;
  tok varchar2(50);
begin
  s := shAdv('iostat -tx', 2000);
  st := alworkshop.StringTokenizer(s, ' ');
  dbms_output.put_line(s);
  while(st.hasNext) loop
    tok := st.nextToken;
    if(instr(tok, '%idle')=1) then
      --idleFound := true;
      exit;
    end if;
  end loop;
  dbms_output.put_line('*******');
  dbms_output.put_line('*' || st.nextToken || '*');
  dbms_output.put_line('*' || st.nextToken || '*');
  dbms_output.put_line('*' || st.nextToken || '*');
  dbms_output.put_line('*' || st.nextToken || '*');
  dbms_output.put_line('*' || st.nextToken || '*');
  dbms_output.put_line('*' || st.nextToken || '*');
end;
/