SQL> select level from dual connect by level <= 3;
     LEVEL
----------
         1
         2
         3

3 rows selected.

SELECT LEVEL
FROM  dual
CONNECT BY LEVEL <= number_of_records_needed

WITH yourtable AS
(
  SELECT 'ABCCATDEFDOFGHIRATJKLCOWMNOBATPQR' thetext FROM dual
)
SELECT SUBSTR( thetext
             , 1+(LEVEL-1)*3-- start position
             , 3 -- string length
             ) str
FROM   yourtable
CONNECT BY LEVEL <= CEIL(LENGTH(thetext)/3)
/

SELECT SUBSTR('CATDOGRATPATMAT',LEVEL*2+(LEVEL-2),3) AS TEXT
FROM DUAL
CONNECT BY LEVEL <= LENGTH('CATDOGRATPATMAT')/3;