APEX

Slightly complex, but bear with me.


On page load, a process runs where I am creating a collection based upon a query and I use an if block to clear the collection is previously exists:

IF HTMLDB_COLLECTION.COLLECTION_EXISTS (p_collection_name => l_collectionName ) THEN
HTMLDB_COLLECTION.DELETE_COLLECTION (p_collection_name => l_collectionName );
END IF;

I then re-create/create the collection where my query is stored in a local block variable l_query:

HTMLDB_COLLECTION.CREATE_COLLECTION_FROM_QUERY( p_collection_name => l_collectionName, p_query => l_query );

-I know this query works, it is valid and returns rows (most of the time, but in this case it does)
-I receive no errors
-I have a report on the page that displays the data based on the collection created and it is correct.
-I output the query via htp just to make sure the query I expect is there
-I also define the name of this collection once, with a constant varchar2, l_collectionName CONSTANT VARCHAR2(25) := 'NAMES_COLLECTION';

I then run an AJAX request to the server (completely custom). It runs in the same application, via application on demand process. The database does some magical parsing to the data that is passed to it. I verify this data with htp.p output to make sure it is clean and correct.

I then check to see if the data I am going to add already exists in the database by doing a SELECT and catching the exception with a WHEN NO_DATA_FOUND error. If the select passes, I know the data does not need to be added. If it receives “no data” found I jump to the exception block (verified via htp output) and then attempt to do:

HTMLDB_COLLECTION.ADD_MEMBER( p_collection_name => l_collectionName, p_c001 => p_firstName, p_c002 => p_lastName );

Which causes an error saying:
"ORA-20102: Application collection NAMES_COLLECTION does not exist"


If I do an if-block in the procedure called by the AJAX request, I receive feed back saying my collection does not exist.

The code I am calling is all in the same PL/SQL package.
It is all called from the same schema user
Its is called with the same session id from/to the same application
I know that the data is initially created in the collection as I receive no errors

and, oddly enough, it works if I restart the database and over time it stops working.

I am completely left without words. Is there something about on demand processes that cause problems with collections? Or is there something wrong with me doing an HTMLDB_COLLECTION.ADD_MEMBER in an exception block? Or is this a database transaction problem?