Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ruben.

Asked: December 23, 2011 - 5:38 am UTC

Last updated: September 16, 2012 - 4:16 am UTC

Version: 11.2.0.1.0

Viewed 10K+ times! This question is

You Asked

Hello Tom,

I'm obviously missing something here, but I can't figure out why
the ORA-01722: invalid number occurs.

Do you know why?

Thank you.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

RSC101@ouds > create table test_tb
  2  ( test_col   varchar2(9) );

Table created.

RSC101@ouds > create table error_tb
  2  ( error_col   varchar2(9) );

Table created.

RSC101@ouds > insert into test_tb (test_col) values ('123456789');

1 row created.

RSC101@ouds > insert into test_tb (test_col) values ('x23456789');

1 row created.

RSC101@ouds > --
RSC101@ouds > insert into error_tb (error_col) values ('x23456789');

1 row created.

RSC101@ouds > commit;

Commit complete.

RSC101@ouds > create or replace view filter_errors_v as
  2  select test_col
  3  from   test_tb
  4  minus
  5  select error_col
  6  from   error_tb;

View created.

RSC101@ouds >
RSC101@ouds > select * from filter_errors_v;

TEST_COL
---------
123456789

RSC101@ouds > select to_number(substr( test_col , 1, 1)) sub1
  2  from   filter_errors_v;

      SUB1
----------
         1

RSC101@ouds >
RSC101@ouds > select * from (
  2  select to_number(substr( test_col , 1, 1)) sub1
  3  from   filter_errors_v
  4  ) where sub1 > 0;
select to_number(substr( test_col , 1, 1)) sub1
                 *
ERROR at line 2:
ORA-01722: invalid number


RSC101@ouds >

and Tom said...

because of predicate pushing and view merging. parenthesis do not cause set operations to happen one after the other or anything like that in sql - sql is not a procedural language. 

select * from (
  2  select to_number(substr( test_col , 1, 1)) sub1
  3  from   filter_errors_v
  4  ) where sub1 > 0;


is the same as

select to_number(substr(test_col,1,1)) sub1
  from filter_errors_v
 where to_number(substr(test_col1,1)) > 0


is the same as

select to_number(substr(test_col,1,1)) sub1
  from (select test_col
          from test_tb
         minus
        select error_col test_col
          from error_tb)
 where to_number(substr(test_col1,1)) > 0



is the same as

select to_number( substr(test_col,1,1)) sub1
  from test_tb
 where to_number(substr(test_col,1,1)) > 0
 minus
select substr(error_col,1,1)) sub1
  from error_tb
 where to_number(substr(test_col1,1)) > 0


You can see that in the plan:

ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from (
  2  select to_number(substr( test_col , 1, 1)) sub1
  3  from   filter_errors_v
  4  ) where sub1 > 0;

Execution Plan
----------------------------------------------------------
Plan hash value: 1655715634

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |     4 |    24 |     8  (25)| 00:00:01 |
|   1 |  VIEW                | FILTER_ERRORS_V |     4 |    24 |     8  (25)| 00:00:01 |
|   2 |   MINUS              |                 |       |       |            |          |
|   3 |    SORT UNIQUE       |                 |     4 |    24 |     4  (25)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| TEST_TB         |     4 |    24 |     3   (0)| 00:00:01 |
|   5 |    SORT UNIQUE       |                 |     4 |    24 |     4  (25)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL| ERROR_TB        |     4 |    24 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - filter(TO_NUMBER(SUBSTR("TEST_COL",1,1))>0)
   6 - filter(TO_NUMBER(SUBSTR("ERROR_COL",1,1))>0)


In fact, if you were to run a 10053 optimizer trace on this, you would see in the tracefile:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT TO_NUMBER(SUBSTR("FILTER_ERRORS_V"."TEST_COL",1,1)) "SUB1" 
FROM  
( 
(SELECT "TEST_TB"."TEST_COL" "TEST_COL" 
   FROM OPS$TKYTE."TEST_TB" "TEST_TB" 
  WHERE TO_NUMBER(SUBSTR("TEST_TB"."TEST_COL",1,1))>0)
  MINUS 
(SELECT "ERROR_TB"."ERROR_COL" "TEST_COL" 
   FROM OPS$TKYTE."ERROR_TB" "ERROR_TB" 
  WHERE TO_NUMBER(SUBSTR("ERROR_TB"."ERROR_COL",1,1))>0
)
) "FILTER_ERRORS_V"


see

http://www.oracle.com/technetwork/issue-archive/2005/05-mar/o25asktom-089621.html

the section on "Views and Merging/Pushing" to read more about this.



ops$tkyte%ORA11GR2> select * from (
  2  select case when substr(test_col,1,1) between '0' and '9' then to_number(substr( test_col , 1, 1)) end sub1
  3  from   filter_errors_v
  4  ) where sub1 > 0;

      SUB1
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 1655715634

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |     1 |     6 |     8  (25)| 00:00:01 |
|   1 |  VIEW                | FILTER_ERRORS_V |     1 |     6 |     8  (25)| 00:00:01 |
|   2 |   MINUS              |                 |       |       |            |          |
|   3 |    SORT UNIQUE       |                 |     1 |     6 |     4  (25)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| TEST_TB         |     1 |     6 |     3   (0)| 00:00:01 |
|   5 |    SORT UNIQUE       |                 |     1 |     6 |     4  (25)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL| ERROR_TB        |     1 |     6 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - filter(CASE  WHEN (SUBSTR("TEST_COL",1,1)>='0' AND
              SUBSTR("TEST_COL",1,1)<='9') THEN TO_NUMBER(SUBSTR("TEST_COL",1,1)) END >0)
   6 - filter(CASE  WHEN (SUBSTR("ERROR_COL",1,1)>='0' AND
              SUBSTR("ERROR_COL",1,1)<='9') THEN TO_NUMBER(SUBSTR("ERROR_COL",1,1)) END >0)



You'll need to use techniques like this anytime (everytime) you convert a string to a number (or date) and the string might not be convertible at some level.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Overcome Invalid Number error by using regexp_like

Vijay Mahawar, April 02, 2012 - 3:18 am UTC

Hello Tom,
Thanks for the wonderful explanation and similarity between all the circumstance of "Views and Pushing/Merging".

One possible solution which which worked for me, I am sharing here,

select to_number(substr( test_col , 1, 1)) from filter_errors_v
where regexp_like(test_col,'[0-9]','i');

Here I have used regexp_like in the WHERE clause and which had overcome the INVALID number error which I was getting due to "Views and Pushing/Merging".

Thanks,
Vijay
Tom Kyte
April 03, 2012 - 6:18 am UTC

ummm, no, you are getting lucky. You need to use a CASE or DECODE statement around the to_number to short circuit it


as I demonstrated above.

a simple plan change and your attempt will fail again.

REGEXP_SUBSTR in SELECT clause

Vijay Mahawar, April 02, 2012 - 3:29 am UTC

Another query which worked is was follows:

SELECT *
FROM (SELECT TO_NUMBER (REGEXP_SUBSTR (test_col, 1, 1)) sub1
FROM filter_errors_v
)WHERE sub1 > 0

Here I used REGEXP_SUBSTR in the select clause.

Thanks,
Vijay.
Tom Kyte
April 03, 2012 - 6:18 am UTC

see above, this is not the correct approach.

and regexp_substr is a lot more expensive than the method I demonstrated above.

any hint helpful?

A reader, April 05, 2012 - 1:15 am UTC

Hi tom,

Is there any hints useful to handle this kind of issue?

I know some like NO_MERGE, NO_PUSH_PRED, MATERIALIZE but seems not work during my testing.

Also, I am thinking inevitably some DEVs do not care whether it is a VIEW and real TABLE,
they just query and notice the result is pure number,
so they know 'oh, they are all number, i can use to_number anywhere',
but does not aware the MERGING and PUSING is happening underneath, then how to overcome this?
Tom Kyte
April 06, 2012 - 9:49 am UTC

case or decode, I've stated that over and over and over and I'll be very constant in that.

I will never, no one should ever, go the hint route on this, that would be a "not smart" approach.


then how to
overcome this?

education.

some developers don't understand the limitations of floats and doubles either and get messed up big time with them. How to overcome that? Education.

Ravi Kumar, September 12, 2012 - 6:52 am UTC

Tom,

We have a situation which I'll explain with a simple example of a query below



select * from MyTable
where
first_where_clause = 3
and varchar2_column = 4;

Now, this code has run in Production systems in our client because till this point the CBO has always decided to use the "first_where_clause=3" as the 'driving' where clause.

But recently it started using "varchar2_column = 4" as the 'driving' where clause, causing an INVALID_NUMBER error.

Now, that said, it is a programmer bug, but my question is, other than a code review, is there a way of identifying this bug in PL/SQL code?

I was thinkg the CBO could have identified it when it 'trials' a lot of different combinations of query, but does it? If it does, is there a way to get the CBO to output the error?

Regards

Ravi
Tom Kyte
September 14, 2012 - 6:28 pm UTC

the CBO cannot output the error, there isn't any error until the implicit conversion actually happens. that where clause is the same as:

where first_where_clause = 3 and TO_NUMBER(varchar2_column) = 4;

if you write your code in the best language possible to write your code in - you can have it warn you (or even make it an error if you want!!)


ops$tkyte%ORA11GR2> alter session set Plsql_Warnings = 'enable:all';

Session altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t ( x varchar2(30), y number );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace procedure p
  2  as
  3  begin
  4          for x in (select * from t where y = 3 and x = 4 )
  5          loop
  6                  null;
  7          end loop;
  8  end;
  9  /

SP2-0804: Procedure created with compilation warnings

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> show errors
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit P omitted optional AUTHID clause; default value
         DEFINER used

4/44     PLW-07204: conversion away from column type may result in
         sub-optimal query plan


whenever you see the implicit conversion - you know you are subject to this issue.

you can "protect" yourself from this by

where y = 4 and (case when y=4 then to_number(varchar2_column) end) = 3;


A reader, September 15, 2012 - 11:14 am UTC

but what if there are rows where y=4 and x has alphanumeric value, your condition below
will give error upon conversion to number. I think it need to test whether all are digit than only apply
to_number() and even best don't store number in varchar or put it in quote. i.e. '3'

"where y = 4 and (case when y=4 then to_number(varchar2_column) end) = 3;"

Thanks.
Tom Kyte
September 16, 2012 - 4:16 am UTC

I think their "business rule" is such that "when y=4, we have stored a number in varchar2_column"

their assumption is all data would be convertible at that point. If it isn't - that is a data integrity issue and you BETTER get an error so you know about it.



The best thing would be to use numbers to store numbers, strings to store strings and dates to store dates and never to have to use to_number/to_date in a where clause - only as a formatting step in the select list - I agree with that.

they shouldn't be comparing a string to a number, the should have compared a string to a string. But then they have to be careful about how they store their data still.

'3'
'3.0'
'03'

are all the number three when converted to a number but are three different strings...


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.