oracle报错06502,一个存储过程报错ORA-06502character string buffer too small

本帖最后由 FrancescoLongo 于 2014-6-15 11:15 编辑

vdrug_code        varchar2(20);

vCnt15               integer;

vprimary_value     NUMBER(1);

vclass_code         varchar2(20);

vitem_code1       varchar2(20);

vpresc_date1      date;

vpresc_no1         NUMBER(12);

vCnt16               integer;

select count(*) into vCnt15

from control_dict

where clinic_item_code = vdrug_code and item_class='A' and primary_value is not null;

if vCnt15 = 1 then

select primary_value into vprimary_value

from comm.yb_special_control_dict

where clinic_item_code = vdrug_code and item_class='A' and primary_value is not null;

if vprimary_value = 0 then

select class_code into vclass_code

from control_dict

where clinic_item_code = vdrug_code and item_class='A';

select count(*) into vCnt16

from control_dict

where class_code = vclass_code and primary_value='1' and item_class='A';

if vCnt16 > 0 then

select item_code into vitem_code1

from control_dict

where class_code = vclass_code and primary_value='1' and item_class='A';

vdrug_code := vitem_code1;

end if;

end if;

end if;

表control_dict结构

item_class       VARCHAR2(2) not null,

item_code        VARCHAR2(10),

item_name        VARCHAR2(40),

clinic_item_code VARCHAR2(10) not null,

clinic_item_name VARCHAR2(40),

jzk_code         VARCHAR2(20),

class_code       VARCHAR2(20),

primary_value    NUMBER(1) default 0

把上面那段屏蔽，运行的时候就不会报ORA06502。

实在是不清楚哪个变量的长度或者精度不够。麻烦高手给看看。

PS：测试环境是10G，运行不报错。

正式环境是11G，就报06502