The problem column is a number (I convert it to a number when selecting from the collection)For me it seems your problem is source data for collection.
And error is raised from that TO_NUMBER
SELECT TO_NUMBER(c001) AS my_col1 FROM apex_collections WHERE collection_name = 'MY_COL'
This unequivocably drops ALL collections for the session, leaving nothing behind. If you accidentally misspell a collection name, the delete will still execute, it just doesn't tell you it didn't find anything to do. This one is nice because it clears out the entire session.
What kind prof you have on this ? Do you have personal experience about that ?
All we know right now is that the continued use of DELETE for the same collection is producing sporadic results and we can't trust the collection contents.
Carol_M wrote:Hi Carol,
hi Jari -- I tend to agree w/ you about not using the cursor. I guess the pertinent question regarding this "invalid number"
error that occurs on rare occasion is: is there a reliable way to clear all data from the collection? It seems that deleting the collection before recreating it does not always do the trick -- possibly because of issues w/ the execution plan like Udo mentioned. Is there any reason to think a truncate would be more effective?
What query hint would restrict to the pertinent collection (specified in the where clause on the apex_collections query)
before gather any other (possibly problem) data?
SELECT /*+ NO_QUERY_TRANSFORMATION */