Idea Summary
APEX_COLLECTIONS are limited to 5 NUMBER and 5 DATE columns which can lead to SQL errors.

Use Case
Once the 5 numeric or date columns have been exhausted, developers have to resort to using the C00x columns and performing explicit conversion which can still cause errors as the underlying WWV_FLOW_COLLECTION_MEMBERS$ table is shared by all applications and the optimizer may pull in other sessions non-numeric/date values in that same column even when the “active” collection only has valid numbers/dates in that column.

I know we can add the exception handler syntax to the TO_NUMBER/DATE function to default the value to NULL on conversion error but this feels like a workaround rather than a robust solution and can lead to genuine data issues in the “active” collection being masked.

Note: I see that the foreign key on WWV_FLOW_COLLECTION_MEMBERS$ has no index on it which makes it more prone to table locks/full table scans - I imagine this was this a conscious decision taken perhaps to reduce the index maintenance overhead on inserts?

Preferred Solution (Optional)
Increase the number of NUMBER and DATE columns to 20 each or as many as you like, why not 50 so it's consistent with the character columns? Should an index be added to the foreign key on the members table?

I see FR-2358 was raised and closed for something very similar and I've no doubt there have been many other similar ideas raised in the past (I must be bad at searching though as I couldn't see them :-)) but I think this warrants further consideration.