Thursday, 14 March 2013

APEX Collections and Joins

APEX Collections is one of the best features of APEX. There are situations where solutions wouldn't be possible if this feature would not be available. However, the collections have some limitations. One of the limitations is if you need to store number values and use those values to join a collection with other tables. The consequences may be that you receive something like

ORA-01722: invalid number


The strange thing with this error is that it may occur sporadic and you can't realy understand why it happens. If you operate on different systems (development, testing, production) you may receive this error on one of the instances and never on the other one. I asked Patrick Wolf about the reasons and he answered that this has to do with the way the cost based optimizer works. If it uses a different plan from the one you would expect, it may find non numeric values in the same column comming from a different collection. In that case the solution is to use the numeric columns in the collection (n0xx) for storing numeric values used in later joins.
on

5 comments:

  1. Robert Schäfer15 March 2013 at 11:19

    Hello Denes,

    thanks for your interesting posting. I also hit on this phenomenon after upgrading an apex application from 4.1 to 4.2.1, which heavivly uses collections.

    It "drove me nearly mad", that this collection issue was not reproducible, but affected the production.
    But the error messages appeared more and more less and after 3 days the spook was over. Now the application runs fine (without changes).

    Reckoning this circumstances and your information in addition: perhaps an intensive *analyse tables* run on the apex repository could solve this issue?

    Best regards
    Robert Schaefer

    ReplyDelete
  2. Lev15 March 2013 at 19:59

    Hello Denes,

    It could happen not only in joins.

    And analyze tables will not help in this situation.

    We had something like:

    select c001*1.5 FROM APEX_collections
    WHERE collection_name = ....

    and it was not easy to figure out why it fails sometimes.

    I think they should add to APEX documentation
    that APEX_COLLECTIONS is actually a view and Oracle can choose
    full table scan any time it thinks it's effective.


    Thanks,

    Lev

    ReplyDelete
  3. Lev15 March 2013 at 20:33

    I'm sorry
    The query was:

    select ... FROM APEX_collections
    WHERE collection_name = ....
    and c001*1.5 = ...

    Lev

    ReplyDelete
  4. Denes Kubicek20 March 2013 at 08:20

    Robert,

    I don't think so.

    Lev,

    my problem was quite similar to yours.

    Thanks for the feedback.

    Regards,

    Denes

    ReplyDelete
  5. Robert Schäfer21 March 2013 at 11:44

    Hello Denes, hello Lev,

    i thought about this issue some more. Too much to write this in a comment, but i created a seperate blogpost for this:

    http://robert-schaefer.blogspot.de/2013/03/spooky-ora-01722-invalid-number-errors.html

    Best regards
    Robert

    ReplyDelete