oracle ora06502,遇到一个很奇怪的ORA-06502：数字或值错误

--配对查询

procedure pair_friend(

p_sex         in         number,

p_city        in         n_3dpassport.city%type,

p_p_code      out        n_3duser.code%type,

p_p_nick      out        n_3dpassport.nickname%type,   --此列长度为20

p_p_portrait  out        n_3dpassport.portrait%type,

p_p_city      out        n_3dpassport.hcity%type

) is

l_p_sex      varchar2(4);

l_order      varchar2(4);

type record_p_attr is record(

id               n_3dpassport.id%type,

nickname         n_3dpassport.nickname%type,

sex              number(1),

age              number(3),

hprovince        n_3dpassport.hprovince%type,

hcity            n_3dpassport.hcity%type,

province         n_3dpassport.province%type,

city             n_3dpassport.city%type,

portrait         n_3dpassport.portrait%type,

code             n_3duser.code%type

);

r_p_attr        record_p_attr;

e_06502     exception;

PRAGMA EXCEPTION_INIT(e_06502,-06502);

begin

--获取配对性别

if p_sex = 0 then --性别女，配对性别男

l_p_sex := '男';

l_order := 'asc';

elsif p_sex = 1 then --性别男，配对性别女

l_p_sex := '女';

l_order := 'desc';

end if;

--配对规则顺序 ，在线的 1 异性  2同城

select t.* into r_p_attr from (

select a.id,a.nickname,decode(a.sex,'男',0,'女',1) sex,

trunc(months_between(sysdate,a.birthdate)/12,0) age,

a.hprovince,a.hcity,a.province,a.city,a.portrait,b.code

from n_3dpassport a,n_3duser b

where a.id=b.passport_id

and b.useronline=1 and a.sex=l_p_sex and (a.city =p_city or a.city =p_city)

order by dbms_random.value) t

where rownum=1;

p_p_code:=r_p_attr.code;

p_p_nick:=r_p_attr.nickname;

p_p_portrait:=r_p_attr.portrait;

p_p_city:=nvl(r_p_attr.city,'保密');

dbms_output.put_line(p_p_code);

dbms_output.put_line(p_p_nick);

dbms_output.put_line(p_p_portrait);

dbms_output.put_line(p_p_city);

exception

when NO_DATA_FOUND then

begin

--如果同城异性没有配对成功

if l_order = 'desc' then

select t.* into r_p_attr from (

select a.id,a.nickname,decode(a.sex,'男',0,'女',1) sex,

trunc(months_between(sysdate,a.birthdate)/12,0) age,

a.hprovince,a.hcity,a.province,a.city,a.portrait,b.code

from n_3dpassport a,n_3duser b

where a.id=b.passport_id

and b.useronline=1 order by sex desc,dbms_random.value ) t

where rownum=1;

elsif l_order = 'asc' then

select t.* into r_p_attr from (

select a.id,a.nickname,decode(a.sex,'男',0,'女',1) sex,

trunc(months_between(sysdate,a.birthdate)/12,0) age,

a.hprovince,a.hcity,a.province,a.city,a.portrait,b.code

from n_3dpassport a,n_3duser b

where a.id=b.passport_id

and b.useronline=1 order by sex asc,dbms_random.value ) t

where rownum=1;

end if;

p_p_code:=r_p_attr.code;

p_p_nick:=r_p_attr.nickname;

p_p_portrait:=r_p_attr.portrait;

p_p_city:=nvl(r_p_attr.city,'保密');

dbms_output.put_line(p_p_code);

dbms_output.put_line(p_p_nick);

dbms_output.put_line(p_p_portrait);

dbms_output.put_line(p_p_city);

exception

when NO_DATA_FOUND then

p_p_code:=0;

p_p_nick:='0';

p_p_portrait:='0';

p_p_city:='0'; --配对不成功

dbms_output.put_line(p_p_code);

dbms_output.put_line(p_p_nick);

dbms_output.put_line(p_p_portrait);

dbms_output.put_line(p_p_city);

when e_06502 then

dbms_output.put_line(r_p_attr.nickname);

dbms_output.put_line(r_p_attr.nickname);

dbms_output.put_line(lengthb(r_p_attr.nickname));

dbms_output.put_line(lengthb(r_p_attr.nickname));

end;

end pair_friend;