APEX Collection Loading in Pre-Rendering Process: Error: WWV_FLOW_COLLECTIONS_UK

Question

I am running a delete/creation of an APEX Collection from pre process (code below) and am running into the following error once in a blue moon.

I saw a couple posts on this error, but they seem to be related to a different issue. There were some comments about the error being caused by the user multi clicking a button that runs this process twice, but this is an on load PL/SQL process.

Has anyone been able to fix this error consistently?

ORA-00001: unique constraint (FLOWS_030000.WWV_FLOW_COLLECTIONS_UK) violated

IF APEX_COLLECTION.COLLECTION_EXISTS('DWLOAD') then

APEX_COLLECTION.DELETE_COLLECTION (
   'DWLOAD');
   
end if;

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
    p_collection_name => 'DWLOAD',
    p_query           => 'select a.seq, 
b.PWSID,  
b.pws_pop_served_count 

from RELATED_PWS a, SYSTEM_INFO b
where a.fk_pwsid = b.pwsid and  seq=  ' ||  :P77_SEQ || '');

Execution options for the Pre Process > "Once per page visit"

Thanks,

Update::

So I was able to replicate it by rapid clicking on the link to load the page. So then the page load runs multiple times. I am looking to see if I can show processing on the href link. For button page submissions I added a DA with Show Processing Page submit which seems to block the multi click.

Side note, be aware you're not doing your SQL pool any favours with the way you've formulated your query. It's hard parsing each time instead of using the bind variable. Just include the :P77_SEQ in your string. — Scott, Mar 23 '21 at 23:18

mohamad subhi bouchi · Answer 1 · 2021-03-24T07:54:16.303

0

maybe use server-side condition will solve the problem.

plsql -> IF APEX_COLLECTION.COLLECTION_EXISTS('collection_name') then 
            return false; 
         ELSE 
            return true;
         END IF;

edited Mar 24 '21 at 07:54

answered Mar 23 '21 at 08:03

mohamad subhi bouchi

180
2
11

1

Maybe? How? What would it do? – Scott Mar 23 '21 at 23:17
I had the same problem, I used server side condition for that. I said maybe because it was also confusing for me!! – mohamad subhi bouchi Mar 24 '21 at 07:48
I do want to delete and recreate the collection on page load, so idk if this will 100% work. Im thinking of adding a new page item and setting that item whenever I redirect to the page. – theCJCsoccer Mar 26 '21 at 01:33

APEX Collection Loading in Pre-Rendering Process: Error: WWV_FLOW_COLLECTIONS_UK

1 Answers1