Skip to Main Content
  • Questions
  • Appending to a CLOB with a 4-byte Chinese character in an AL32UTF8 database raises ORA-22921

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tim.

Asked: March 31, 2021 - 7:53 am UTC

Last updated: April 07, 2021 - 4:09 am UTC

Version: 19c

Viewed 100+ times

You Asked

Hi there,

Is there a setting I can tweak in the database to adjust this behaviour and thus avoid the error?

There's a few workarounds, but I need* to avoid an application code change.

Through Oracle Support this has been raised as Bug 32703286 - DBMS_LOB.WRITEAPPEND FAILS WITH ORA-22921 WHILE PROCESSING SPECIAL CHARACTERS.

Ideally this bug would be fixed by July and we can patch our database(s) but we're not holding our breath and are asking the customer to be patient.

Thanks,
Tim

* With the planned development team workload, I can possibly implement the workaround this calendar year but my management is reluctant to authorise work to implement a workaround for a bug when we have much more important things to assign my time to.

with LiveSQL Test Case:

and we said...

I'm not entirely sure what your test case was trying to achieve (with the trim/substr etc) but initialised 'c' seemed to help


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>
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    c := 'x';
 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
LOB LENGTH() 0 - "; "

PL/SQL procedure successfully completed.

SQL>
SQL>


More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database