SQL> with test as
  2    (select 'hi hello how are you doing out there, need your help!' col from dual)
  3  select substr(col, 35 * (level - 1) + 1, 35) result
  4  from test
  5  connect by level <= length(col) / 35 + 1;

RESULT
-----------------------------------
hi hello how are you doing out ther
e, need your help!

SQL>

SQL> col col format a35
SQL> select 'hi hello how are you doing out there, need your help!' col from dual;
COL
-----------------------------------
hi hello how are you doing out ther
e, need your help!

DECLARE
l_str VARCHAR2(200):='hi hello how are you doing out there, need your help!';
l_len integer:=35;
BEGIN
while LENGTH(l_str)<>0 loop 
dbms_output.put_line(substr(l_str,1,l_len));
l_str:=substr(l_str,l_len+1);
end loop;
end;

COLUMN token FORMAT a50;
COLUMN token_length FORMAT 99;
SET LINESIZE 500;
SET PAGESIZE 200;

WITH tmptab AS
(
    -- length = 45
    SELECT '111111111111111111111111111111111111111111111' AS colval 
    FROM dual UNION ALL
    -- length = 5
    SELECT '22222' AS colval 
    FROM dual UNION ALL
    -- length = 35
    SELECT '33333333333333333333333333333333333' AS colval 
    FROM dual UNION ALL
    -- length = 34
    SELECT '4444444444444444444444444444444444' AS colval 
    FROM dual UNION ALL
    -- length = 36
    SELECT '555555555555555555555555555555555555' AS colval 
    FROM dual UNION ALL
    -- length = 71
    SELECT '66666666666666666666666666666666666666666666666666666666666666666666666' AS colval 
    FROM dual UNION ALL
    -- length = 80
    SELECT '77777777777777777777777777777777777777777777777777777777777777777777777777777777' AS colval 
    FROM dual
)
SELECT  substr
        (
            t1.colval, 
            ((t2.column_value - 1) * 35) + 1, 
            35
        ) token,
        length
        (
            substr
            (
                t1.colval, 
                ((t2.column_value - 1) * 35) + 1, 
                35
            )
        ) token_length,
        length(t1.colval) colval_length
FROM    tmptab t1 CROSS JOIN 
        TABLE
        (
            CAST
            (
                MULTISET
                (
                    SELECT level
                    FROM dual
                    CONNECT BY level <= ceil(length(t1.colval) / 35)
                ) AS sys.odciNumberList
            )
        ) t2;




TOKEN						   TOKEN_LENGTH COLVAL_LENGTH
-------------------------------------------------- ------------ -------------
11111111111111111111111111111111111			     35 	   45
1111111111						     10 	   45
22222							      5 	    5
33333333333333333333333333333333333			     35 	   35
4444444444444444444444444444444444			     34 	   34
55555555555555555555555555555555555			     35 	   36
5							      1 	   36
66666666666666666666666666666666666			     35 	   71
66666666666666666666666666666666666			     35 	   71
6							      1 	   71
77777777777777777777777777777777777			     35 	   80
77777777777777777777777777777777777			     35 	   80
7777777777						     10 	   80

13 rows selected.

SQL> 

SQL> CREATE OR REPLACE TYPE strtype AS TABLE OF VARCHAR2 (255);
  2  /
 
Type created
 
SQL> 
SQL> CREATE OR REPLACE FUNCTION STR2SPLIT
  2         (P_STR       IN VARCHAR2,
  3          P_DELIM_LEN IN NUMBER)
  4  RETURN STRTYPE
  5  AS
  6     I_STR VARCHAR2(255);
  7     I_DELIM_LEN NUMBER;
  8     I_DATA STRTYPE := STRTYPE();
  9  BEGIN
 10     I_STR := P_STR;
 11     I_DELIM_LEN := P_DELIM_LEN;
 12     WHILE LENGTH(I_STR) <> 0 LOOP
 13        I_DATA.EXTEND;
 14        I_DATA(I_DATA.COUNT) := SUBSTR(I_STR, 1, I_DELIM_LEN);
 15        I_STR := SUBSTR(I_STR, I_DELIM_LEN + 1);
 16  
 17     END LOOP;
 18  
 19     RETURN I_DATA;
 20  END;
 21  /
 
Function created
 
SQL> SELECT *
  2    FROM TABLE(CAST(STR2SPLIT('hi hello how are you doing out there, need your help!hi hello how are you doing out there, need your help!hi hello how are you doing out there, need your help!hi hello how are you doing out there, need your help!',
  3                              30) AS STRTYPE));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
hi hello how are you doing out
 there, need your help!hi hell
o how are you doing out there,
 need your help!hi hello how a
re you doing out there, need y
our help!hi hello how are you
doing out there, need your hel
p!
 
8 rows selected

with t as (
           select  'hi hello how are you doing out there, need your help!hi hello how are you doing out there, need your help!hi hello how are you doing out there, need your help!hi hello how are you doing out there, need your help!' str,
             30 chunk_size
             from  dual
          )
select  substr(str,chunk_size * (chunk_num - 1) + 1,chunk_size) chunk
  from  t,
        xmltable(
                 '1 to xs:integer(.)'
                 passing ceil(length(str) / chunk_size)
                 columns
                   chunk_num number path '.'
                )
  order by chunk_num
/

CHUNK
------------------------------
hi hello how are you doing out
 there, need your help!hi hell
o how are you doing out there,
 need your help!hi hello how a
re you doing out there, need y
our help!hi hello how are you
doing out there, need your hel
p!

8 rows selected.

SQL>