1 2 Previous Next 27 Replies Latest reply: Oct 4, 2010 2:00 PM by joelkallman-Oracle Go to original post RSS
      • 15. Re: invalid number querying collection
        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.
        I assume you do something like
        SELECT TO_NUMBER(c001) AS my_col1 
        FROM apex_collections 
        WHERE collection_name = 'MY_COL'
        And error is raised from that TO_NUMBER


        Edited by: jarola on Oct 1, 2010 12:44 AM

        Or it might be app NLS settings, how conversion is done, what is decimal separator for your session ...
        • 16. Re: invalid number querying collection
          The example I posted was just to illustrate the combined use of the create_or_truncate command. You are welcome to use whatever method of populating the query you want. One thing I would point out however, is that a truncate is inherently faster than a delete and is guaranteed to remove all data. The truncate tells the collection it is now empty, rather than individually removing lines from the collection.

          Another thing that can help you track down the problem is to use different collection names for different pages in your application. Another collection handler you might also be interested in is:
          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.

          Before going to a lot of trouble to create a whole bunch of custom collection-handling procedures in the database, I'd recommend taking advantage of all the ones that are already there.
          • 17. Re: invalid number querying collection

            I do not agree that when we talink APEX_COLLECTIONS.

            If you have your own table, truncate is faster than delete.

            All collections are stored to one table, so I do not think APEX_COLLECTION.CREATE_OR_TRUNCATE really use TRUNCATE command...

            Of course I'm not sure and expert on this are, but this is my understanding.
            And if you check view source Udo post

            • 18. Re: invalid number querying collection
              I do not agree that when we talink APEX_COLLECTIONS.
              Can you be more clear? Some of your posts are very difficult to understand (the spellchecker can be very handy), thus I may be misinterpreting something.

              Again, I'm not trying to tell you how to do anything. It's been my experience in working with collections for over four years that these are some things that work and may address the issues in the question. But I am not going to claim they are the perfect solution or that they are appropriate for all situations. We are trying to build a solution. If we already knew what it was, we wouldn't be here. So before just out-of-hand disregarding the suggestions, try them out and see if they work. 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. The two command suggestions I have made help eliminate this as a potential issue and help get us closer to the root cause, which is very likely in the data itself.
              • 19. Re: invalid number querying collection

                I mean,
                I do not agree that CREATE_OR_TRUNCATE_COLLECTION is faster or safer to use than DELETE_COLLECTION.
                But I do not mean say "do not use CREATE_OR_TRUNCATE_COLLECTION".
                I did just mean bring another point to this as you have
                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.
                What kind prof you have on this ? Do you have personal experience about that ?
                How we can know what OP is really doing and what is source data or actual code ?

                And I do not mean anything bad with that. And sorry , my English is really bad.


                Edited by: jarola on Oct 1, 2010 1:46 AM
                • 20. Re: invalid number querying collection
                  Hi Jari,

                  I agree with you.
                  There is a difference between the two commands that should be considered. For the ease of use I cite the documentation:

                  - "If you delete a collection, you delete the collection and all of its members."
                  - "You can remove session state of a collection by calling the CREATE_OR_TRUNCATE_COLLECTION method [...]"

                  I guess this could be an explanation for the fact, that deleting appears to be slower than truncating, because the deletion will wait for the data in the collection-members table (wwv_flow_collection_members$) to be deleted for that session and then delete the collection from wwv_flow_collections$, whereas the truncate just affects session state and probably causes deletion in the table only by side-effects in background.
                  Considering that, it appears to be more reliable to use the delete. But that's just my interpretation.

                  • 21. Re: invalid number querying collection
                    Carol_M wrote:
                    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?

                    Hi Carol,

                    I still assume that other sessions use the same collection with other data types in different reports/pages/... or, what Jari pointed out, you have some values not fitting to your NLS parameters, but I guess you already checked the latter aspect.
                    I don't think, either problem can be solved by truncating or deleting for a session, because this always affects only that certain session.
                    One hint would be the one shown in my edit post for the view above. You could try a less restrictive one, but I think that collections should always be retrieved exactly by the way the view is formulated and the optimizer should not do any transformation on the view, even if that would be considered to be faster.
                    You could use that hint in each read-access-query in your application if you don't want to modify the view. But obviously this is more work and there may be API-calls using collections that would not use the hint and cause troubles again.
                    Here the select example from above again:
                     SELECT /*+ NO_QUERY_TRANSFORMATION */
                    • 22. Re: invalid number querying collection
                      All -- Thanks, I've learned a lot from this exchange.

                      Udo, I've put the hint into all my queries on collections, and though of course I can't prove
                      it, it appears to be doing the trick. Previously, I could pretty quickly generate the invalid number
                      error by having 2 sessions going and creating the collection IR_COLLECTION with different types
                      of data in the two different sessions. After implementing the hint, I tried extensively to generate
                      the error and couldn't. I'm going to run with this for now. Hopefully we'll move away from using
                      collections, but I'm glad to have this safety net.

                      If it breaks, I may be back :)

                      • 23. Re: invalid number querying collection
                        Hi Carol,

                        that's good news. I hope this actually helps and I'd be interested to know if it really does on the long run. Currently I wouldn't have other ideas on what to do, except trying the workarounds that have been suggested in this thread.

                        This problem caused us severe headaches in one of our projects. Regrettably, the developer didn't see these problems while working on the application, because he focussed on functional requirements, and because was the only developer working on the APEX-part of the project. When it came to roll-out tests the trouble started, but at that point time was very limited already. There have been other (smaller) issues too, so analysis took a big part of the remaining days, before tracing got us to the guess about the execution plan.
                        We tried the patch with the view and things seemed to have gotten stable. But we couldn't use the change with the view for production, because the security rules of the data center that produced the application for several customers didn't allow that - which I understand from their point of view.
                        Unfortunately we didn't have enough time to rewrite the whole application in order to include hints in the queries, and to do tests with API-functions used to be sure the problem wouldn't arise in production, so we didn't see other chances than moving from collections to a "safer" approach.
                        Our risk analysis forbid to try the hints in later projects, because the additional efforts for rewriting again would have been too high if they didn't work, especially in API-calls using collections.
                        So if using the hint really does the trick, perhaps I could adjust the risk estimation and try to go back to collections again. It's much easier to use and has quite good API-support.

                        Have a nice weekend,

                        • 24. Re: invalid number querying collection
                          1) Udo is exactly correct as to the cause of this issue and the remedy. Collection members for all users across all workspaces are ultimately maintained in one table. The APEX_COLLECTION view constrains the rows to those in your workspace in your application in your APEX session and with a specific collection name. Sometimes, the query optimizer will consider rows belonging to other collections - and that needs to be avoided (via the method Udo suggests).

                          2) In APEX 4.0, there are now 5 columns of native type DATE and 5 columns of native type NUMBER, which can be used to avoid this issue altogether.

                          I hope this helps.

                          • 25. Re: invalid number querying collection
                            Hi Joel,

                            thanks for your confirming feedback.This may help us to take a chance on using collections again, though I'm not sure that all APIs using collections will do fine without applying the hint to the view, which is not applicable for us in all projects.

                            As for the native types in the collection: I actually didn't look on collections so close that I realized that change. For most of our projects, this will not change much, because the majority of columns would be filled with numbers.

                            Though I know this may not be regarded as an official statement, I'll dare to ask anyway: Is there a chance such hint will ever make it into the standard? If not, is it because something inside APEX might struggle with it, or just because the problem is not common enough to risk the possible loss of performance?

                            • 26. Re: invalid number querying collection
                              Udo -- Yes, I'll definitely let you know if this continues to work. We may actually be making
                              a fundamental shift in development direction that would eliminate use of collections... but
                              if not, I'm sure there will be plenty of opportunities to continue to assess the effectiveness of
                              this solution.

                              Joel, thanks for the tip on 4.0 collections. As with Udo, we have numerous instances where
                              5 columns wouldn't suffice, but good to know. What are the columns whose native types
                              have changed?

                              • 27. Re: invalid number querying collection
                                @Udo +"Is there a chance such hint will ever make it into the standard?"+ It could, and I've thought about that. In general, though, it's bad practice to embed a hint in the definition of a view - which is why we've avoided it up until now.

                                @Udo and @Carol - It's fine that you use more than 5 numeric columns in your collections. This query optimizer issue only seems to surface when you are doing an implicit or explicit character to number conversion in the query against the collection itself (i.e., in your SELECT clause or in your WHERE clause).

                                1 2 Previous Next