how do I change regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm') to only read 132 characters each time how do I change the query below so it only reads and displays 132 characters each line

with w(id, line#, line, rest) as (
  select id, 1, regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'), 
         substr(to_char(bar), regexp_instr(bar, '$', 1, 1, 1, 'm')+1)
  from foo
  union all
  select id, line#+1, regexp_substr(rest, '^.*?$', 1, 1, 'm'), 
         substr(rest, regexp_instr(rest, '$', 1, 1, 1, 'm')+1)
  from w
  where rest is not null ) cycle id, line# set is_cycle to '1' default '0'
select id, line#, line from w order by id, line#;


Thanks
This should work:

with w(id, line#, line, rest) as (
  select id, 1, SUBSTR(regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'), 1, 132), 
         substr(to_char(bar), LEAST(132, regexp_instr(bar, '$', 1, 1, 1, 'm'))+1)
  from foo
  union all
  select id, line#+1, SUBSTR(regexp_substr(rest, '^.*?$', 1, 1, 'm'), 1, 132),
         substr(rest, LEAST(132, regexp_instr(rest, '$', 1, 1, 1, 'm')+1))
  from w
  where rest is not null ) cycle id, line# set is_cycle to '1' default '0'
select id, ROW_NUMBER() OVER (ORDER BY line#) AS line#, line
from w
where TRIM(line) &lt;&gt; ''
order by id, line#;


What it does is:


line: truncate (SUBSTR(..., 1, 132)) the first full line to the first 132 characters (which does nothing for lines shorter than 132)
rest: grab the rest of the CLOB of either the first full line, or whatever comes after 132 characters, whichever starts first (substr(rest, LEAST(132, ...)))




Response to the sequence question in comments:

To create a sequence over a partition, your best bet is self-join with a grouped-and-sorted:

SELECT t.MO_ID, g.DOC_NO, ...
FROM &lt;your table&gt; t
JOIN (
  SELECT DISTINCT ROWNUM AS DOC_NO, MO_ID
  FROM &lt;your table&gt;
  WHERE &lt;same as in outer query&gt;
  ORDER BY MO_ID
  ) g ON (g.MO_ID = t.MO_ID)
WHERE ...


or with a CTE + ROW_NUMBER() window function:

WITH
  t AS (
    SELECT *
    FROM &lt;your table&gt;
    WHERE ...
    ),
  g AS (
    SELECT MO_ID, ROW_NUMBER() OVER (ORDER BY MO_ID) AS DOC_NO
    FROM (SELECT DISTINCT MO_ID FROM t) x
    )
SELECT t.MO_ID, g.DOC_NO, ...
FROM t
JOIN g ON (g.MO_ID = t.MO_ID)