title VARCHAR2(300);
subtitle VARCHAR2(300);
author VARCHAR2(50);
keywords VARCHAR2(200);
description VARCHAR2(1000);
source VARCHAR2(200);
pv NUMBER(12);
comment_num NUMBER(12);
file_num NUMBER(12);
has_link VARCHAR2(1);
is_publish VARCHAR2(1);
is_lock VARCHAR2(1);
is_elite VARCHAR2(1);
pos NUMBER(12);
year NUMBER(12);
start_date DATE;
expire_date DATE;
create_date DATE;
create_user_id NUMBER(12);
modify_user_id NUMBER(12);
modify_date DATE;
publish_user_id NUMBER(12);
sts VARCHAR2(1);
is_indexed VARCHAR2(1);
is_public VARCHAR2(1);
marker VARCHAR2(20);
id number(12);
article_id number(12);
category_id number(12);
r_content_clob clob;
buffer long;
amount number := 2000;
offset number := 1;
tmp number(12);
tmpcode varchar2(200);
cursor cur is select id,title,subtitle,author,keywords,description,source,pv,comment_num,file_num,has_link,is_publish,is_lock,is_elite,pos,year,start_date,expire_date,create_date,create_user_id,modify_user_id,modify_date,publish_user_id,sts,is_indexed,marker,is_public from s_portal_article@dblink where id in(select article_id from s_portal_category_article@dblink where category_id in (select id from s_portal_category@dblink where site_id=570 and sts='Y'));
begin
for n_cs in cur loop
title:=n_cs.title;
subtitle:=n_cs.subtitle;
author:=n_cs.author;
keywords:=n_cs.keywords;
description:=n_cs.description;
source:=n_cs.source;
pv:=n_cs.pv;
comment_num:=n_cs.comment_num;
has_link:=n_cs.has_link;
is_publish:=n_cs.is_publish;
is_lock:=n_cs.is_lock;
is_elite:=n_cs.is_elite;
file_num:=n_cs.file_num;
pos:=n_cs.pos;
year:=n_cs.year;
start_date:=n_cs.start_date;
expire_date:=n_cs.expire_date;
create_date:=n_cs.create_date;
create_user_id:=n_cs.create_user_id;
modify_user_id:=n_cs.modify_user_id;
modify_date:=n_cs.modify_date;
publish_user_id:=n_cs.publish_user_id;
sts:=n_cs.sts;
is_indexed:=n_cs.is_indexed;
marker:=n_cs.marker;
id:=n_cs.id;
insert into s_portal_article(id,title,subtitle,author,keywords,description,source,pv,comment_num,file_num,has_link,is_publish,is_lock,is_elite,pos,year,start_date,expire_date,create_date,create_user_id,modify_user_id,modify_date,publish_user_id,sts,is_indexed,marker,is_public) values(seq_app.nextval,title,subtitle,author,keywords,description,source,pv,comment_num,file_num,has_link,is_publish,is_lock,is_elite,pos,year,start_date,expire_date,create_date,create_user_id,modify_user_id,modify_date,publish_user_id,sts,is_indexed,marker,is_public);
select max(id) into article_id from s_portal_article;
execute immediate 'select content from temp_table where article_id=:1' into r_content_clob using id;
--select content into r_content_clob from temp_table where article_id=id;
/*if r_content_clob is null then
update TEMP_TABLE set CONTENT='buffer' where ARTICLE_ID=id;
return;
end if;*/
amount:=dbms_lob.getlength(r_content_clob);
dbms_lob.read(r_content_clob,amount,offset,buffer);
insert into s_portal_article_content(id,article_id,content) values(seq_app.nextval,article_id,buffer);
dbms_output.put_line(id);
--select id into category_id from s_portal_category where name in (select name from s_portal_category@dblink where id in( select category_id from s_portal_category_article@dblink where article_id=id));
execute immediate 'select category_id from s_portal_category_article@dblink where article_id=:1' into tmp using id;
dbms_output.put_line('1+++++:'||tmp);
execute immediate 'select code from s_portal_category@dblink where id =:1' into tmpcode using tmp;
dbms_output.put_line('2+++++:'||tmpcode);
execute immediate 'select id from s_portal_category where code=:1 and SITE_ID=:2' into category_id using tmpcode,siteid;
dbms_output.put_line('3+++++:'||category_id);
insert into s_portal_category_article(id,category_id,article_id,is_up,is_allup,sts) values(seq_app.nextval,category_id,article_id,'Y','Y',sts);
end loop;
commit;
end;
小弟从一个数据库拷贝数据到另一个数据库，执行上面的存储过程，编译能通过，执行时报ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小ORA-06512: 在 "SYS.DBMS_LOB", line 759
ORA-06512: 在 "HCOA_1.DATACOPY", line 76
ORA-06512: 在 line 2，其中标红线部分为76行。敢问是哪错啦。
5 个解决方案
#1
--肯定是你变量那些的长度不够，导致的错误
--比如：
DECLARE
str VARCHAR2(1);
BEGIN
str:= SubStr('CV1JASXXXXXCXXXXXXXXM9162(单围条)', 27, 1) ;
Dbms_Output.put_line(str);
END;
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 4
Total execution time 0.031 sec.
#2
是这个buffer VARCHAR2(32767);导致的，但是我已经把它定义到最大啦
#3
是把clob字段读取到buffer中，然后通过insert插入到数据库中
#4
你看看你需要插入的表的字段长度是不是过短了.....
#5
插入表是clob类型，不会短的
#1
--肯定是你变量那些的长度不够，导致的错误
--比如：
DECLARE
str VARCHAR2(1);
BEGIN
str:= SubStr('CV1JASXXXXXCXXXXXXXXM9162(单围条)', 27, 1) ;
Dbms_Output.put_line(str);
END;
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 4
Total execution time 0.031 sec.
#2
是这个buffer VARCHAR2(32767);导致的，但是我已经把它定义到最大啦
#3
是把clob字段读取到buffer中，然后通过insert插入到数据库中
#4
你看看你需要插入的表的字段长度是不是过短了.....
#5
插入表是clob类型，不会短的