请教一个字符串连接的SQL语句ORA-06502错误：数字或值错误

[superc1z]

DECLARE

type r_gsa is record(group_id group_sub.group_id%TYPE,acc_id group_sub.acc_id%TYPE,city_id group_sub.city_id%TYPE);



mysql varchar2(100);
unpayfee NUMBER(14,2);


cursor c_gsa is SELECT DISTINCT group_id,acc_id,city_id FROM group_sub where service_id<>50001 and rec_status=1;

begin
for r_gsa in c_gsa loop

mysql := 'select unpay_fee '||'into unpayfee ' || ' from acc_bill_' || TO_CHAR(r_gsa.city_id) || 'where bill_month='||''''||to_char(add_months(SYSDATE,-1),'YYYYMM')||''''||' and acc_id='||r_gsa.acc_id ;
--上面一行，一加上最后的||r_gsa.acc_id就报ORA-06502错误：数字或值错误



dbms_output.put_line(mysql);




end loop;










end;




其中r_gsa.acc_id就是group_sub.acc_id 是varchar2(40)类型的
那条连接语句一加||r_gsa.acc_id就报错ORA-06502错误：数字或值错误

[bzszp]

r_gsa.acc_id 输出看看，里面存放的是什么东西

[superc1z]

2003531014
1200121662
2028823562
2028000012
2008083200
2028435013
1200123562
2006228385
2030604680
1200276721
1200361663
1200359880
2030608240
2027999979
2027954665
2009331054
2009340486
2028823682
2028823526
2014926975
2028053125
2030608071
2030607189
2027998907
2027998395

就是类似这些的数字

[superc1z]

哦，找到原因了，原来是mysql长度不够长，改成200就好了，谢谢各位

[nowait]

mysql := 'select unpay_fee '||'into unpayfee ' || ' from acc_bill_' || TO_CHAR(r_gsa.city_id) || 'r_gsa （加上别名，另外你在这里少了空格）where bill_month='||''''||to_char(add_months(SYSDATE,-1),'YYYYMM')||''''||' and acc_id='||r_gsa.acc_id ;

我提供另外一个语法给你，你试试：
mysql := 'select unpay_fee from acc_bill_' || TO_CHAR(r_gsa.city_id) || ' r_gsa where bill_month='||''''||to_char(add_months(SYSDATE,-1),'YYYYMM')||''''||' and acc_id='||r_gsa.acc_id ;

execute immediate mysql into unpayfee ;
dbms_output.put_line(mysql);

[superc1z]

谢谢nowait,不过正要请教这个问题 mysql若为空怎么办，用execute immediate mysql into unpayfee ;会报取值为空错

[heyixiang]

check mysql

if null then return