Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table1 as 
select 
123  as NOTE_NUMBER, 1 as LINE_NUMBER, 'ABC' as NOTE_TEXT
union all select
123, 2, 'DEFGHIJ'
union all select
456, 1, 'ABCDEE'
3 rows affected

create table table2 as 
select 123 as NOTE_NUMBER
union all
select 456
2 rows affected

WITH checkLength AS
(SELECT 
  t1.NOTE_NUMBER, 
  LISTAGG(t1.NOTE_TEXT) AS wholeText, 
  LENGTH(LISTAGG(t1.NOTE_TEXT)) AS wholeLength
FROM table1 t1
INNER JOIN table2 t2 
  ON t1.NOTE_NUMBER = t2.NOTE_NUMBER
GROUP BY t1.NOTE_NUMBER)
SELECT * FROM checkLength
WHERE wholeLength < 7;


NOTE_NUMBER
WHOLETEXT
WHOLELENGTH


456
ABCDEE
6


SELECT 
  t1.NOTE_NUMBER, 
  LISTAGG(t1.NOTE_TEXT) AS wholeText, 
  LENGTH(LISTAGG(t1.NOTE_TEXT)) AS wholeLength
FROM table1 t1
INNER JOIN table2 t2 
  ON t1.NOTE_NUMBER = t2.NOTE_NUMBER
GROUP BY t1.NOTE_NUMBER
  HAVING LENGTH(LISTAGG(t1.NOTE_TEXT)) < 7;


NOTE_NUMBER
WHOLETEXT
WHOLELENGTH


456
ABCDEE
6