ORA-22160 并非是稀梳数组引起来的

ORA-22160: element at index [1] does not exist

昨天调了过程 搞了好久才找到原因.

procedure stat_month_to_tmp

cursor cur_state_month_tmp is
select
id as order_id,
provinceid,
areaid
typeid,
info_id,
o.code,
ordername,

from ....

begin

open cur_state_month_tmp;
loop
fetch cur_state_month_tmp bulk collect into
l_ary_ID,
l_ary_PROVINCEID,

l_ary_TYPEID,
l_ary_INFO_ID,
l_ary_CODE,
l_ary_ORDERNAME;

l_rowcnt:=l_ary_ID.count;
l_rowcnt:=l_ary_ID(1);
forall x in 1..l_ary_ID.count
merge into delv_order_stat_mid using dual
on (id =l_ary_id(x))
when matched then
update set
complettime =l_ary_complettime(x),
mail_succ_send =mail_succ_send+l_ary_mail_succ_send (x),
sms_succ_send =sms_succ_send+l_ary_sms_succ_send (x),
error =l_ary_error (x),
TASKSTATUS =L_ARY_TASKSTATUS(x),

when not matched then

insert into oss_delv_order_stat_mid
(
id,
provinceid,
areaid,
typeid,
info_id,
code,
ordername,
insert_time,
modfiy_time

)
values(
l_ary_id (i),
l_ary_provinceid (i),

l_ary_areaid (i),
l_ary_typeid (i),
l_ary_info_id (i),
l_ary_code (i),
l_ary_ordername (i),
sysdate,
sysdate
);

run at merge into delv_order_stat_mid using dual report eror!!!

ORA-22160: element at index [1] does not exist

l_rowcnt:=l_ary_ID.count;
l_rowcnt:=l_ary_ID(1);

都有数据啊 !

后来 好久 好久才发现 多了个 l_ary_areaid(i) 这个在搜集当中没有 fetch cur_state_month_tmp bulk collect into

values(
l_ary_id (i),
l_ary_provinceid (i),

l_ary_areaid (i),

我在收集加上去报 l_ary_ORDERNAME; 类型错误! 反复对照 游标和表的类型,还有核对列数....

后来后来 把游标里的SQL 拿到查询窗口运行 然后复制列名 用ULTRAEDIT 开两个文本 发现 查询出来的少了1列

areaid
没有逗号 查询语句把它给忽略掉. 语法不检查,太怪了!