ORA-01722: invalid number only on select * from view, not against view directly

Question

I'm getting ORA-01722: invalid number but only when I select * from theView but not when I select against the theView directly (using the SQL inside the view's CREATE OR REPLACE...).

(I have faced and understand this error before, as well as running aggregates against NULL values, that one shouldn't store VARCHARS in NUMBER columns, etc. but am struggling to understand this issue)

Answer 1

Usually you get it when oracle executes your filter predicate that should filter only numbers, after predicates where you use it as a number.

Simple example:

create table t as
     select '1' x, 'num' xtype from dual union all
     select 'A' x, 'str' xtype from dual
/
create index t_ind on t(x);

You can see we get ORA-01722 in this very simple example even though we specified filter xtype='num' before x > 0:

select x
from (
     select x
     from t
     where xtype='num'
     ) v
where v.x > 0;

ERROR:
ORA-01722: invalid number

Execution plan:

Plan hash value: 1601196873

---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |      1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1 / T@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((TO_NUMBER("X")>0 AND "XTYPE"='num'))

As you can see from the plan, inline view was merged and both predicates are on the same level.

Now compare with this:

select/*+ 
           no_merge(v) 
           opt_param('_optimizer_filter_pushdown' 'false')
     */ 
      x
from (
     select x
     from t
     where xtype='num'
     ) v
where v.x > 0;

X
-
1

Execution plan:

Plan hash value: 3578092569

----------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     3 (100)|          |
|*  1 |  VIEW              |      |      1 |     3 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |     6 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2 / V@SEL$1
   2 - SEL$2 / T@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("V"."X")>0)
   2 - filter("XTYPE"='num')

Read more about this: http://orasql.org/2013/06/10/too-many-function-executions/