ORA-22160 is not caused by a thin comb Array

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

It took a long time to figure out the reason for the call yesterday.

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
L_ary_ID,
Rochelle ary_provinceid,

L_ary_TYPEID,
Rochelle ary_info_id,
L_ary_CODE,
L_ary_ORDERNAME;

Rochelle rowcnt: = maid;
Rochelle rowcnt: = maid (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 ),
Rochelle ary_provinceid (I ),

L_ary_areaid (I ),
L_ary_typeid (I ),
Rochelle 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

Rochelle rowcnt: = maid;
Rochelle rowcnt: = maid (1 );

All have data!

After a long time, I found that there was an additional l_ary_areaid (I), which was not found during the collection.Fetch cur_state_month_tmp bulk collect

Values (
L_ary_id (I ),
Rochelle ary_provinceid (I ),

L_ary_areaid (I ),

 

 

I added the l_ary_ORDERNAME; type error to the collection! Compare the cursor and table types repeatedly, and check the number of columns ....

Later, I took the SQL statement in the cursor to the query window and copied the column name to use ULTRAEDIT to open two texts. I found that one column was missing.

Areaid
Ignore it without a comma query statement. The syntax is not checked. It's so strange!