ORA-06502: PL/SQL: 'Numeric or Value Error' When CLOB Convert to VARCHAR2 on a Multibyte Database

每条sql 语句 抓取的varchar2 输出不能超过4000个字节，如果是字符可能1500个左右

这条语句执行ok,截取1500个字符

select content_id,SUB_ID,
POSTER,to_char(post_time, 'YYYY-MM-DD HH24:MI') as DT, DBMS_LOB.SUBSTR(REPLY_CONTENT,1500,1) as REPLY_CONTENT
,minetype,CREATE_DATE,
SUB_PIC
from sub_content
where sub_id = '441' order by dt

 

这条语句失败，截取1501 个字符，  

ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小

select content_id,SUB_ID,
POSTER,to_char(post_time, 'YYYY-MM-DD HH24:MI') as DT, DBMS_LOB.SUBSTR(REPLY_CONTENT,1500,1) as REPLY_CONTENT,
DBMS_LOB.SUBSTR(REPLY_CONTENT,1502,1501) as REPLY_CONTENT1
,minetype,CREATE_DATE,
SUB_PIC
from sub_content
where sub_id = '441' order by dt

 

另外还有一种可能，就是apex 在更新完数据库后 表单无法显示该字段，可以看看这个字段的标识是varchar2 ,还是clob，可以设置成clob

 SELECT  MAIN_CONTENT,DBMS_LOB.GETLENGTH(MAIN_CONTENT) FROM  main_subject where subject='fyi'

 

 

下面是举例：

ORA-06502: PL/SQL: 'Numeric or Value Error' When CLOB Convert to VARCHAR2 on a Multibyte Database

On a database with multibyte characterset like AL32UTF8 specified for NLS_CHARACTERSET the following error is received when a CLOB which contains more than 8191 characters is assigned to a VARCHAR2 variable.

ORA-06502: PL/SQL: numeric or value error when CLOB convert to VARCHAR2

You can reproduce the error with the below code:

declare
     VARCHAR2_32767     VARCHAR2(32767) := NULL ;
     V_CLOB             CLOB            ;
begin
     for i in 1..32767 loop
        V_CLOB           := V_CLOB || 'A';
     end loop;
     /* The below statement fails if data in clob more than
      8191 characters in multibyte characterset environment */
    VARCHAR2_32767   := V_CLOB ; 
end;

 

Exception Stack:

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 10

CAUSE

Bug 11893621 DBMS_LOB.SUBSTR CAN TRUNCATE DATA OVER 8191 BYTES LONG IN MULTIBYTE CHARACTERSET

SOLUTION

This issue is fixed in version 11.2.0.3.0 or later. For earlier versions, download and apply the Patch 9020537 for your version and platform.

Or

To workaround this situation you can programmatically read 8191 characters sequentially and append them to the VARCHAR2 variable using DBMS_LOB.READ function.

declare
     VARCHAR2_32767     VARCHAR2(32767) := NULL ;
     V_CLOB                     CLOB ;
     buffer varchar2(8191):= null;
     amount number :=8191;
     offset number :=1;
     length number;
     
  begin
     for i in 1..32767 loop
        V_CLOB := V_CLOB || 'A';
     end loop;

     length := dbms_lob.getlength(v_clob);

while offset < length loop
       dbms_lob.read(v_clob, amount, offset, buffer);
       VARCHAR2_32767 := VARCHAR2_32767||buffer;
       offset := offset + amount;
end loop;
     
end;
/