CREATE OR REPLACE PROCEDURE tokenizer (
         iStart     IN       NUMBER,
         sPattern   IN       VARCHAR2,
         sBuffer    IN       VARCHAR2,
         sResult    OUT      VARCHAR2,
         iNextPos   OUT      NUMBER
        )
AS
     nPos1   NUMBER;
     nPos2   NUMBER;
BEGIN
     nPos1 := INSTR (sBuffer, sPattern, iStart);
    IF nPos1 = 0
    THEN
            sResult := NULL;
    ELSE
           nPos2 := INSTR (sBuffer, sPattern, nPos1 + 1);
          IF nPos2 = 0
          THEN
                 sResult := RTRIM (LTRIM (SUBSTR (sBuffer, nPos1 + 1)));
                 iNextPos := nPos2;
          ELSE
                sResult := SUBSTR (sBuffer, nPos1 + 1, nPos2 - nPos1 - 1);
                iNextPos := nPos2;
          END IF;
     END IF;
END tokenizer;

CREATE OR REPLACE PROCEDURE sp_test_tokenizer
AS
      sepr VARCHAR2(1);
      sbuf VARCHAR2(200);
      sres VARCHAR2(200);
      pos NUMBER;
      istart NUMBER;
BEGIN
 
      sbuf := ‘@0@11@222@3333@44444@555555@6666666@77777777@888888888?;
      sepr := ‘@’;
      istart := 1;
      tokenizer (istart ,sepr,sbuf,sres,pos);
      IF (pos <> 0) THEN
          dbms_output.put_line (sres);
      END IF;
     WHILE (pos <> 0)
     LOOP
             istart := pos;
             tokenizer (istart ,sepr,sbuf,sres,pos );
             dbms_output.put_line (sres);
     END LOOP;
END sp_test_tokenizer;
/

SQL> SET serveroutput ON
SQL>exec sp_test_tokenizer;