ORA-01722: invalid number

I already covered this error in a different situation but let's look at it from an entirely different point of view.

create table test_table (col_A varchar2(10))
/
insert into test_table values('A')
/
insert into test_table values('1')
/
commit
/
select * from test_table
where col_a = 1;

ORA-01722: invalid number

This error occurs because there is an implicit string to number conversion going on here.
As you see i omitted the tick marks around the value 1 in the query, so Oracle is trying to convert the column value from varchar2 into number and then perform the comparison.
As soon as oracle hits against the 'A' value, the conversion fails and the error is raised.

One could think that this kind of errors are of scarce importance because they can be easily spotted, but actually these kind of errors can easily turn themselves into big troubles.

Why?

I was at a site where they created a column of type varchar2 and they kept only numeric codes in it for years.

You can see what happens by removing the record containing the 'A'

delete from test_table where col_a='A'
/

select * from test_table
where col_a = 1;

col_a
1

No errors this time!

One day the people of this company decided to add some alphanumeric codes and the first time they ran a procedure reading data from that table, it blew up.

How can it be, a program that has been running for years!

Well, it's easy, a very trivial error can cause a big mess *years* after it was deployed to production, because it was just working by pure chance.

So, be careful when storing numbers into varchar2 columns, if you don't write your queries properly they are going to transform themselves into ticking bombs, silently awaiting their turn to explode.

See message translations for ORA-01722 and search additional resources

---

ORA-01722: numero non valido
ORA-01722: número no válido
ORA-01722: número no vàlid
ORA-01722: Nombre non valide
ORA-01722: Ungültige Zahl
ORA-01722: μη αποδεκτός αριθμός
ORA-01722: ugyldigt tal
ORA-01722: ogiltigt tal
ORA-01722: ugyldig tall
ORA-01722: virheellinen numero
ORA-01722: nem megengedett szám
ORA-01722: număr eronat
ORA-01722: Ongeldig getal.
ORA-01722: número inválido
ORA-01722: número inválido
ORA-01722: неверное число
ORA-01722: neplatné číslo
ORA-01722: neplatné číslo
ORA-01722: niepoprawna liczba
ORA-01722: geçersiz sayı