Thursday, March 21, 2013

Spooky "ORA-01722: invalid number" errors when using Apex collections

This is a follow-up to Denes Kubicek's blogpost "APEX Collections and Joins". Denes wrote in his blogpost about receiving the error "ORA-01722: invalid number" when querying an APEX collection. What actually astonishing is, that the collection definitely contains just valid numbers and this error is not really reproducible, it just appears sporadic.

I also hit this issue after upgrading an APEX application, which heavily uses APEX collections. After upgrading (to apex version 4.2) the error appeared in all sorts of different places... sometimes... the only constant was, that it only showed up when querying APEX collections. But in my case the errors appeared more and more less and after 2-3 days the spook was over.

Denes blogpost gave me the inspiration to think over this issue again. Although I tried not to reproduce this error again (hell no!), i'm quite sure, that the following is a plausible explanation:

One problem is, that apex collections, due to the structure, are often used with implicit conversions. Number values are often stored in the varchar2-columns of the collection. This is not always immediately visible, especially when joining these columns with other table columns or when  the query is encapsulated by a view. But have a look on this simplified example:

[SQL 1]
select * 
from   apex_collections 
where  collection_name = 'ARTICLES' 
and    c001 = vArticleNo;

The datatype of the column "C001" is "varchar2", the datatype of the variable "vArticleNo" is "number". So Oracle must always convert internally the column value to a number ("implicit conversion"). This is (apparently..) no problem and works fine, as long as you insert only valid numbers in "c001" for your collection.

But keep in mind, that the column "c001" is also used in other collections and probably contains strings there. In this case the following sql would abort with the error "ORA-01722: invalid number":

[SQL 2]
select * 
from   apex_collections 
where  c001 = vArticleNo;

Of course this kind of sql (without filtering the collection name) would be never used in practice. But it's just for understanding the following fact: the first query [SQL 1] can only work, when the Cost-Based Optimizer (CBO) decides to filter the data with "collection_name = 'ARTICLES'" at first. Within this resultset it filters now the data with "c001 = vArticleNo". If the CBO would decide to filter the criterias vice versa, the query would fail with "ORA-01722: invalid numbers", because it will first convert the complete data of the column "c001" to number - including the strings from the other collections!

But when decides the CBO to apply the filter criterias vice versa? APEX stores the collection contents in the table "wwv_flow_collection_members$". If you already implemented some APEX collections, it will contain a lot of histograms for these user data columns:

[SQL 3]
select *
from   dba_histograms
where  owner = 'APEX_040200'
and    table_name = 'WWV_FLOW_COLLECTION_MEMBERS$'
and    column_name like 'C0%'
order by column_name, endpoint_number;

Depending on these table stats, the CBO could decide to apply the filter criterias vice versa. So if you hit this error and you won't touch the program code at first, just try to reanalyse the stats of this repository table as a quick fix. Best choice would be to completely remove the histograms from the user data columns ("dbms_stats.delete_column_stats"). It makes no sense to have histograms on these columns - the content is just temporary and the data characterics changes constantly. In my opinion the histograms for these columns could be completely deactivated ("dbms_stats.set_table_prefs").

If you are using implicit conversions and you're upgrading your apex version, it's more likely, that this error could occurs. The APEX repositiory is newly build and therefore the table stats are probably not optimal yet (see my personal error scenario above).

But back to the example sql. To avoid the implicit conversion here, the datatype of the variable has to be just converted to the datatype of the table column:

[SQL 4]
select * 
from   apex_collections 
where  collection_name = 'ARTICLES' 
and    c001 = to_char(vArticleNo);

Now the CBO can do whatever he wants.

The better way would be just to use the number columns (N001..N005) in APEX collection - like Denes described in his blogpost. Unfortunately only five of them exist.

So, take care not to use unintentionally implicit conversions in collections. Never use implicit conversions at all!


3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi,
    Thanks for this posting. Can you give me some hints as to why my following query may not be working. I am using the sample emp, dept tables that come with Apex 4.2 install. I am using these in context of RESTful Web services, creation and deployment. As part of the RESTful web service example Wizard process, a report is created from a collection that is populated from the WEb Service reference that looks like:

    select c001 "empno"
    , c002 "ename"
    , c003 "job"
    , c004 "mgr"
    , c005 "hiredate"
    , c006 "sal"
    , c007 "comm"
    , c008 "deptno"
    from apex_collections
    where collection_name = 'P2_DOREST_RESULTS'

    I am now trying to join this to the dept table
    using:

    select ac.c001 "empno"
    , ac.c002 "ename"
    , ac.c003 "job"
    , ac.c004 "mgr"
    , ac.c005 "hiredate"
    , ac.c006 "sal"
    , ac.c007 "comm"
    , ac.c008 "deptno"
    , d.dname
    from apex_collections ac , dept d
    where collection_name = 'P2_DOREST_RESULTS'
    and to_char(d.deptno)=to_char(ac.c008)

    I am getting no rows.

    Can you give me an idea as to what I may not be doing correctly?

    ReplyDelete
  3. Hi Pat,

    thanks for your feedback. Due to my easter vacation, I couldn't answer earlier.

    Currently you store your number and date datatypes in the varchar2-columns (C001, C002, ..) of the apex collection. At best, try to avoid this and store the numbers (like deptno) in the number-columns (N001, N002, ..) and the dates (like hiredate) in the date-columns (D001, D002, ..). Now you don't need datatype conversion anymore, the sql could look this:

    select ac.n001 "empno"
    , ac.c001 "ename"
    , ac.c002 "job"
    , ac.n002 "mgr"
    , ac.d001 "hiredate"
    , ac.n003 "sal"
    , ac.n004 "comm"
    , ac.n005 "deptno"
    , d.dname
    from apex_collections ac, dept d
    where collection_name = 'P2_DOREST_RESULTS'
    and d.deptno= ac.n005

    Please note that querying "apex_collections" only output results, when it's implemented in apex environment. If you query it in a tool like SQL Developer, it will show no rows, because your apex session informations are not set here. In this case you could query the tables APEX_040200.WWV_FLOW_COLLECTIONS$ and APEX_040200.WWV_FLOW_COLLECTION_MEMBERS$ directly and check if the collection contains your data at all.

    Cheers,
    Robert

    ReplyDelete