是DBMS_LOB。处理 1 字节>字符时的 SUBSTR 限制

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

问题是，当clob字段中有一些字符占用超过1个字节时，如何使用oracle来查找4000个字符。

Answer 1

问题不在于substr函数。这是SQL中varchar2的最大长度:4000 bytes!

这意味着即使这个语句也会失败:

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

这是真的，因为2001个两个字节的字符是4002字节，这超过了数据类型可以处理的范围。对于这些情况，您应该使用clob。

Answer 2

仅供参考

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")

在32768处，它返回NULL。所以它取决于字符串的EXTENDED属性。