Search code examples
oracle-apex

Oracle Apex Member sequence does not exist in application collection error

I have an Interactive grid and the data populates wit reference to department id selected in dropdown.

So in drop down a dynamic action is also added which creates a collection to pull grids data with only emp_id, dept and bonus columns.

Collection_members:

c001-> emp_id
c002-> dept
c003 bonus

In the process part on click of update button for the grid, i use

Declare
l_seq NUMBER;
Begin
select max(seq_id) into l_seq from apex_collections where collection_name ='AA_COL' and c001 = :EMP_ID and c002 = : DEPT and c003 = :BONUS;
Apex_collection_update_member(p_collection_name => 'AA_COL',p_seq => l_seq,
p_c001 => :EMP_ID and p_c002 => : DEPT and p_c003 => :BONUS);
End;

This works fine, but if BONUS is empty, for that record it throws the error:

The error is only for records where BONUS is null

"Member sequence does not exist in application collection AA_COLL"

How can we fix this type of error?

Solution

  • If BONUS is empty then the select statement will return no rows, so l_seq will be null. You can change the select statement here:

    and (:BONUS is null or c003 = :BONUS)