You need to use LENGTH2 if you are in a UTF database charset, because LOBs are stored in 2byte pairs
SQL> DECLARE
2 t VARCHAR2(700);
3 v VARCHAR2(700);
4 d VARCHAR2(700);
5 c CLOB;
6 BEGIN
7 delete utf8_4char_nodata;
8 INSERT INTO utf8_4char_nodata (title) VALUES (utl_raw.cast_to_varchar2('f0a1918d'));
9 COMMIT;
10
11 SELECT title, DUMP(title,16)
12 INTO t, d
13 FROM utf8_4char_nodata;
14
15 dbms_output.put_line('');
16 dbms_output.put_line(d);
17 dbms_lob.createtemporary(c, TRUE);
18
19 FOR i IN 1..LENGTH(t)
20 LOOP
21 v := SUBSTR(t, i, 1);
22 dbms_output.put_line(v);
23 dbms_output.put_line('LENGTH() '||LENGTH(v)||' LENGTHB() '||LENGTHB(v)||' - '||TO_CHAR(ASCII(v), 'xxxxxxxxxx'));
24 dbms_lob.trim(c, 0);
25 dbms_lob.writeappend(c, length(v), v);
26 v := dbms_lob.substr(c);
27 dbms_output.put_line('LOB LENGTH() '||dbms_lob.getlength(c)||' - "'||v||'; '||TO_CHAR(ASCII(v), 'xxxxxxxxxx')||'"');
28 END LOOP;
29 END;
30 /
Typ=1 Len=4: f0,a1,91,8d
┐
LENGTH() 1 LENGTHB() 4 - f0a1918d
DECLARE
*
ERROR at line 1:
ORA-22921: length of input buffer is smaller than amount requested
ORA-06512: at "SYS.DBMS_LOB", line 1163
ORA-06512: at line 25
SQL> DECLARE
2 t VARCHAR2(700);
3 v VARCHAR2(700);
4 d VARCHAR2(700);
5 c CLOB;
6 BEGIN
7 delete utf8_4char_nodata;
8 INSERT INTO utf8_4char_nodata (title) VALUES (utl_raw.cast_to_varchar2('f0a1918d'));
9 COMMIT;
10
11 SELECT title, DUMP(title,16)
12 INTO t, d
13 FROM utf8_4char_nodata;
14
15 dbms_output.put_line('');
16 dbms_output.put_line(d);
17 dbms_lob.createtemporary(c, TRUE);
18 FOR i IN 1..LENGTH(t)
19 LOOP
20 v := SUBSTR(t, i, 1);
21 dbms_output.put_line(v);
22 dbms_output.put_line('LENGTH() '||LENGTH(v)||' LENGTHB() '||LENGTHB(v)||' - '||TO_CHAR(ASCII(v), 'xxxxxxxxxx'));
23 dbms_lob.trim(c, 0);
24 dbms_lob.writeappend(c, length2(v), v);
25 v := dbms_lob.substr(c);
26 dbms_output.put_line('LOB LENGTH() '||dbms_lob.getlength(c)||' - "'||v||'; '||TO_CHAR(ASCII(v), 'xxxxxxxxxx')||'"');
27 END LOOP;
28 END;
29 /
Typ=1 Len=4: f0,a1,91,8d
┐
LENGTH() 1 LENGTHB() 4 - f0a1918d
LOB LENGTH() 2 - "┐; f0a1918d"
PL/SQL procedure successfully completed.