ora-01722

ORA-1722 is Invalid number.  We've attempted to either explicity or implicity convert a
character string to a number and it is failing.

This can happen for a number of reasons.  It generally happens in SQL only (during a
query) not in plsql (plsql throws a different exception for this error). 

You can see this error easily by:

ops$tkyte@8i> select to_number('abc') from dual;
select to_number('abc') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number


This error seems to creep into queries in the strangest ways.  A change in the order of a
predicate can make it come and go -- depending on the order of evaluation in the
predicate.  Consider this example:

ops$tkyte@8i> create table  t ( x int, y varchar2(25) );

Table created.

ops$tkyte@8i>
ops$tkyte@8i> insert into t values ( 1, 'abc' );

1 row created.

ops$tkyte@8i> insert into t values ( 2, '123' );

1 row created.

ops$tkyte@8i>
ops$tkyte@8i>
ops$tkyte@8i> select * from t where y > 100 and x = 2;

         X Y
---------- -------------------------
         2 123

ops$tkyte@8i> select * from t where x = 2 and y > 100;
select * from t where x = 2 and y > 100
                                *
ERROR at line 1:
ORA-01722: invalid number



The first query worked since we *tend* to evaluate queries from the bottom up.  We
evaluated the x=2 part first and never tried to do 'abc' > 100.  In the second query, the
y>100 was evaluated first.  Y was promoted to a number and then compared to 100.  'abc'
could not be converted so ORA-1722.

The only general purpose solution is to always compare like types to like types.  You
should either convert the column Y entirely to numbers (clean the data) or use a
character string comparision (which changes the meaning of the predicate -- y > 100 is
very different from y > '100' )