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
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:
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.
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;
/
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2017-01-15 PHP 分析1