Oracle SQL: how to concatenate text until reaches maximum size

I have a case:

some script to reproduce:

 create table test_Txt(
 id   number(1),
 text varchar(2000)
 );
 
 insert into test_txt values(1,'BRUG HIDDEN 4. The student is obliged to justify the absence from educational classes within the specified period:
1) parents or legal guardians excuse the student''s absence by the end of the second week of the following month / for the previous month / in justified cases (e.g. a visit to a doctor), the parent or legal guardian may exempt some classes in person or in writing;
2) in the event of a student''s resignation from participation in non-compulsory classes, written information from the parent or legal guardian is required;
3) expected absence of a student for more than one week (e.g. stay in a sanatorium, hospital, chronic disease), parents or legal guardians are obliged to notify the class teacher within 3 days;
4) if the absence is not excused within the prescribed period, the class teacher explains the reasons for the absence with the parents or legal guardians;
5) information about frequent absenteeism of students is passed to the school pedagogue, parents are called
6)xfasdfkjasdgkdsethkgjaskgjbsajgbkjsdguihifafa');
 
 insert into test_txt values(2,'BRUG HIDDEN 4. The student is obliged to justify the absence from educational classes within the specified period:
1) parents or legal guardians excuse the student''s absence by the end of the second week of the following month / for the previous month / in justified cases (e.g. a visit to a doctor), the parent or legal guardian may exempt some classes in person or in writing;
2) in the event of a student''s resignation from participation in non-compulsory classes, written information from the parent or legal guardian is required;
3) expected absence of a student for more than one week (e.g. stay in a sanatorium, hospital, chronic disease), parents or legal guardians are obliged to notify the class teacher within 3 days;
4) if the absence is not excused within the prescribed period, the class teacher explains the reasons for the absence with the parents or legal guardians;
5) information about frequent absenteeism of students is passed to the school pedagogue, parents are called
6)xfasdfkjasdgkdsethkgjaskgjbsajgbkjsdguihifafa');
 
 insert into test_txt values(3,'BRUG HIDDEN 4. The student is obliged to justify the absence from educational classes within the specified period:
1) parents or legal guardians excuse the student''s absence by the end of the second week of the following month / for the previous month / in justified cases (e.g. a visit to a doctor), the parent or legal guardian may exempt some classes in person or in writing;
2) in the event of a student''s resignation from participation in non-compulsory classes, written information from the parent or legal guardian is required;
3) expected absence of a student for more than one week (e.g. stay in a sanatorium, hospital, chronic disease), parents or legal guardians are obliged to notify the class teacher within 3 days;
4) if the absence is not excused within the prescribed period, the class teacher explains the reasons for the absence with the parents or legal guardians;
5) information about frequent absenteeism of students is passed to the school pedagogue, parents are called
6)xfasdfkjasdgkdsethkgjaskgjbsajgbkjsdguihifafa');
  
 insert into test_txt values(4,'BRUG HIDDEN 4. The student is obliged to justify the absence from educational classes within the specified period:
1) parents or legal guardians excuse the student''s absence by the end of the second week of the following month / for the previous month / in justified cases (e.g. a visit to a doctor), the parent or legal guardian may exempt some classes in person or in writing;
2) in the event of a student''s resignation from participation in non-compulsory classes, written information from the parent or legal guardian is required;
3) expected absence of a student for more than one week (e.g. stay in a sanatorium, hospital, chronic disease), parents or legal guardians are obliged to notify the class teacher within 3 days;
4) if the absence is not excused within the prescribed period, the class teacher explains the reasons for the absence with the parents or legal guardians;
5) information about frequent absenteeism of students is passed to the school pedagogue, parents are called
6)xfasdfkjasdgkdsethkgjaskgjbsajgbkjsdguihifafa');

 insert into test_txt values(5,'BRUG HIDDEN 4. The student is obliged to justify the absence from educational classes within the specified period:
1) parents or legal guardians excuse the student''s absence by the end of the second week of the following month / for the previous month / in justified cases (e.g. a visit to a doctor), the parent or legal guardian may exempt some classes in person or in writing;
2) in the event of a student''s resignation from participation in non-compulsory classes, written information from the parent or legal guardian is required;
3) expected absence of a student for more than one week (e.g. stay in a sanatorium, hospital, chronic disease), parents or legal guardians are obliged to notify the class teacher within 3 days;
4) if the absence is not excused within the prescribed period, the class teacher explains the reasons for the absence with the parents or legal guardians;
5) information about frequent absenteeism of students is passed to the school pedagogue, parents are called
6)xfasdfkjasdgkdsethkgjaskgjbsajgbkjsdguihifafa');

 insert into test_txt values(6,'BRUG HIDDEN 4. The student is obliged to justify the absence from educational classes within the specified period:
1) parents or legal guardians excuse the student''s absence by the end of the second week of the following month / for the previous month / in justified cases (e.g. a visit to a doctor), the parent or legal guardian may exempt some classes in person or in writing;
2) in the event of a student''s resignation from participation in non-compulsory classes, written information from the parent or legal guardian is required;
3) expected absence of a student for more than one week (e.g. stay in a sanatorium, hospital, chronic disease), parents or legal guardians are obliged to notify the class teacher within 3 days;
4) if the absence is not excused within the prescribed period, the class teacher explains the reasons for the absence with the parents or legal guardians;
5) information about frequent absenteeism of students is passed to the school pedagogue, parents are called
6)xfasdfkjasdgkdsethkgjaskgjbsajgbkjsdguihifafa');

And Select:

SELECT t.*,FLOOR((sum_text)/3000)
        FROM   (SELECT id,
                       TO_CHAR(text) AS text,
                       LENGTH(text) AS len_Text,
                       SUM(LENGTH(text)) OVER(ORDER BY id DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_text
                FROM   test_txt) t;
        

What i want to achieve is concatenate texts until sum of them is less than 3000. If larger, then another text.

ID  LEN_TEXT    SUM_TEXT    FLOOR((SUM_TEXT)/3000)
6   1039    1039    0
5   1039    2078    0
4   1039    3117    1
3   1039    4156    1
2   1039    5195    1
1   1039    6234    2

And here goes problem: texts with group 1 when You sum them is greater than 3000. Text with id = 2 supposed to have group 2 but is 1.

Anyone has an idea? Thanks

Answer

From Oracle 12, you can use MATCH_RECOGNIZE:

SELECT id,
       LENGTH( text ) AS text_len,
       match_num,
       SUM( LENGTH( text ) ) OVER ( PARTITION BY match_num ) AS total_len
FROM   test_txt
MATCH_RECOGNIZE(
  ORDER BY id
  MEASURES
    MATCH_NUMBER() - 1 AS match_num
  ALL ROWS PER MATCH
  PATTERN ( a+ )
  DEFINE
    A AS SUM( LENGTH( text )) <= 3000
)

Which, for your sample data, outputs:

ID	TEXT_LEN	MATCH_NUM	TOTAL_LEN
1	1039	0	2078
2	1039	0	2078
3	1039	1	2078
4	1039	1	2078
5	1039	2	2078
6	1039	2	2078

db<>fiddle here