Hello.
We are using Oracle Apex 21.2.6.
We have an modal page IR and are selecting all rows in it and want to put selected values into a collection.
The solution works, but for some (random) rows returns this error:
Error Stack: ORA-00001: unique constraint (APEX_210200.WWV_FLOW_COLLECTION_MEMBERS_PK) violated
Error happens in this section:
if l_seq_id = 0 then
apex_collection.add_member(
p_collection_name => l_collection_name,
p_n001 => l_value );
Error appears random. As sometime it works, other time 4 rows return violation, sometimes 2 rows. Row values (f01) are unique. For example: 1529, 1658, 3213, 3214, 2638, 2089, 1972, 1754
How can we solve this? I added testcase (in first answer).
Code we are using...
Javascript first checks each row and calls UpdateCollection function:
var elements = document.getElementsByName('f01');
for(var i = 0; i < elements.length; i++) {
if(!elements[i].checked) {
f_UpdateCollection(elements[i].value, 2);
}else
{
f_UpdateCollection(elements[i].value, 1);
}
}
UpdateCollection function calls application process like:
function f_UpdateCollection(cb, stat){
apex.server.process (
"UpdateCheckboxValue"
, { x01: cb,
x02: 'COLL_NAME',
x03: stat
}
Process looks something like:
IF APEX_APPLICATION.g_x03 = 1 THEN
begin
select seq_id into l_seq_id
from apex_collections
where collection_name = l_collection_name
and n001 = l_value;
exception
when no_data_found then
l_seq_id := 0;
end loop;
--
if l_seq_id = 0 then
apex_collection.add_member(
p_collection_name => l_collection_name,
p_n001 => l_value );
end if;
Thanks.
BR,
Dip