Try this one for CLOB sizes bigger than VARCHAR2:
We have to split the CLOB in parts of "VARCHAR2 compatible" sizes, run lengthb through every part of the CLOB data, and summarize all results.
declare
my_sum int;
begin
for x in ( select COLUMN, ceil(DBMS_LOB.getlength(COLUMN) / 2000) steps from TABLE )
loop
my_sum := 0;
for y in 1 .. x.steps
loop
my_sum := my_sum + lengthb(dbms_lob.substr( x.COLUMN, 2000, (y-1)*2000+1 ));
-- some additional output
dbms_output.put_line('step:' || y );
dbms_output.put_line('char length:' || DBMS_LOB.getlength(dbms_lob.substr( x.COLUMN, 2000 , (y-1)*2000+1 )));
dbms_output.put_line('byte length:' || lengthb(dbms_lob.substr( x.COLUMN, 2000, (y-1)*2000+1 )));
continue;
end loop;
dbms_output.put_line('char summary:' || DBMS_LOB.getlength(x.COLUMN));
dbms_output.put_line('byte summary:' || my_sum);
continue;
end loop;
end;
/