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.


Robert Schäfer said...

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

Lev said...

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.



Lev said...

I'm sorry
The query was:

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


Denes Kubicek said...


I don't think so.


my problem was quite similar to yours.

Thanks for the feedback.



Robert Schäfer said...

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:

Best regards