Is DBMS_LOB.SUBSTR limitation in dealing with characters which are > 1 byte

Question

create table t ( x int, y clob );

create or replace procedure p( p_x in int, p_new_text in varchar2 )
    as
    begin
            insert into t values ( p_x, p_new_text );
    end;
    /

exec p(1, rpad('*',4002,'*') );

exec p(2, rpad('é',4002,'é') );

select x, dbms_lob.getlength(y) from t;

select x, dbms_lob.substr(y,4000,1) from t where x = 1; -- this one does not give error and function correctly finds 4000 characters as each character 1 byte

select x, dbms_lob.substr(y,4000,1) from t where x = 2; -- function not able to find 4000 characters and gives error as é is more than 1 byte.
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

Question is what to use from oracle to find 4000 characters when there can be some characters in the clob field which occupy more than 1 byte.

Answer 1

The problem isn't really the substr function. It is rather the maximum length of a varchar2 in SQL: 4000 bytes!

That means than even this statement will fail:

select x, dbms_lob.substr(y,2001,1) from t where x = 2

That is true since 2001 characters of two bytes is 4002 bytes, which is more than the data type can handle. For these cases, you should use clob.

Answer 2

FYI, under 21c

select index_name, column_position, column_expression, dbms_lob.substr(sys_dburigen(index_owner, index_name, column_position, column_expression, 'text()').getclob(),32767,1) as ce
from all_ind_expressions
;

UOM_UNIQUE_LONG_NAME    1   UPPER("UNIT_OF_MEAS_NAME")  UPPER("UNIT_OF_MEAS_NAME")
UOM_UNIQUE_SHORT_NAME   1   UPPER("SHORT_NAME") UPPER("SHORT_NAME")

At 32768, it returns NULL. So it depends on the EXTENDED attributes for string.