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
没有逗号 查询语句把它给忽略掉. 语法不检查,太怪了!