ORA-06502: "Numeric Or Value Error: Character String Buffer Too Small" from SQL Using DBMS_LOB.SUBSTR

On one occasion support teams reached out to us with an strange error ORA-06502: "Numeric Or Value Error: Character String Buffer Too Small" from SQL Using DBMS_LOB.SUBSTR. 

They mentioned to get this error only when data is more than 4000 bytes and lesser values it works fine. MOS already have this problem listed under Doc ID 567574.1.

Reason of this error:

The DBMS_LOB.SUBSTR returns a VARCHAR2 and VARCHAR2 cannot be > 4000 bytes. The data being returned is from a UTF8 database and the Column BIGCOL contains multibyte characters which result in the converted size exceeding 4000 bytes.

The following can be used as a work around:

    create or replace function my_clob_substr( c in clob) return varchar2

    as

    v_characters number := 4001;

    v_length number := 4001;

    begin

    while v_length > 4000 loop

    v_characters := v_characters-1;

    v_length := lengthb(dbms_lob.substr(c,v_characters,1));

    end loop;

    return dbms_lob.substr(c,v_characters,1);

    end;

    /

It works by stripping characters until the lengthb (byte length) <= 4000 and ensures that the converted size is not greater than 4000 bytes.

Change the call from:

    select DBMS_LOB.SUBSTR(BIGCOL,3990,1)

    from <table>

    WHERE ID = <value>

with:

    select my_clob_substr(BIGCOL)

    from <table>

    WHERE ID = <value>