Skip to Main Content

ORA-20101: Application collection exists & ORA-20104: create_collection_from_query Error

I ran into this error when filling a collection on an Apex login page, which takes a few seconds. Every once in a while, the user would try to log in twice in a row, and get a "ORA-20101: Application collection exists" error. This error is in turn caused by "ORA-20104: create_collection_from_query Error".

The code looked like this:
The scenario:
On the first login, delete_collection completes, and then the create_collection_from_query starts.
The query fed to create_collection_from_query is slow, so while that is still running, the user logs in (i.e. submits the page) again.
On the second login, delete_collection does not run (because there is no collection yet), and then the create_collection_from_query starts.
By the time the second login finishes, the first create_collection_from_query has already completed and has created the collection - hence the exception.

I didn't find any real solutions on the internet - some came close but were not exactly elegant, like adding an exception "when other then null" and then trying to create the collection again...
I did find a 10 year old community.oracle.com thread that mentions the problem (but doesn't answer it): https://community.oracle.com/thread/603860. I liked the phrase "if the user becomes impatient and starts pressing the button that creates the collection multiple times then I get the following error" - that described my problem perfectly :-)

I fixed this in two ways.
First, I rewrote the query statement to be faster, and also used create_collection_from_query_b (which is faster than create_collection_from_query); that should give the user less time to login twice.
And I added exception handling, to make sure that the error is not raised if the collection can't be created during the first login. Note that it will succeed with the second login, so in the end all is well.

The new code: