Monday, March 19, 2007

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ı

30 comments:

Unknown said...

nice post! thanks. it's solved a tricky problem of mine. Simply explained too.

Ashwath said...

Thanks a lot mate.

Anonymous said...

Thanks, this answered a question that i've been trying to find the answer to for quite some time.

Hamish said...

I've been trying to figure this out all afternoon for a remote client. Thanks for the nice and simple explanation.

Blackfoot said...

Thanks and that solved my problem too. It was a pesky problem and I just wasn't seeing the simply, stupid reason my script was erroring. Thanks.

Anonymous said...

I'm not 100% sure, but there seems to be some difference in this behavior between 9i and 10g. We have two 10g systems on which this error never cropped up, but when I ran the same query on a 9g system is failed. Thanks for the post, you saved me some time on this one.

Byte64 said...

I'm glad that people find these articles useful.

Thanks for your comment,
Flavio

Anonymous said...

I had some variation of the issue explained that really threw me for a loop. I am definitely no Oracle guru, but am constantly creating views used for reporting.
One of our views converts a string to a number ie; to_number(stringfield)
The conversion is made so that another table can be joined. The field in the table has a datatype of NUMBER(7). When the value was included in a WHERE clause, the aformentioned error would result.
The modification that I made that stopped the error was to_number(stringfield,'0000000'). Even though both values were numbers, I guess that wasn't enough- they had to have the same amount of possible digits? Anyway, thanks for the helpful site.
elm

Anonymous said...

Thanks a lot.I had spent hours trying to solve this problem.

Anonymous said...

Thanks for the hint.
I have experienced a variation of this, thats even more scary: A new execution plan can make the bomb go of even with the same data. Patching oracle unleashed the bug.

Anonymous said...

We experience a variation of the problem on 10gR2/Solaris without having a clue why - here is the case:

our table is
SQL> desc t_securities
Name Null? Type
----------------------------------------- -------- ----------------------------
REQUEST_ID NOT NULL NUMBER(19)
REQUEST_SUBID NOT NULL NUMBER(7)
RIC NOT NULL VARCHAR2(30 CHAR)
PROVIDER VARCHAR2(10)
RATEDATE_REQ VARCHAR2(8)
RATETIME_REQ VARCHAR2(8)
PRICE_TYPE VARCHAR2(3)
ISIN VARCHAR2(12)
MARKET VARCHAR2(3)
RATEDATE VARCHAR2(11 CHAR)
RATETIME VARCHAR2(5 CHAR)
CURRENCY VARCHAR2(3)
PRICE NUMBER(20,7)
PRICE_ADD_ON VARCHAR2(4)
RETURN_CODE VARCHAR2(2)


our c++-program issues the following statement using OTL (ie OCI):
SELECT REQUEST_SUBID, CURRENCY, PRICE, PRICE_ADD_ON, to_date(nvl(RATEDATE, '01 Jan 0001')), RATETIME, RETURN_CODE, systimestamp
AT TIME ZONE 'UTC', '2' FROM T_SECURITIES WHERE REQUEST_ID = :bvRequestId
(with bvRequestId being of type long)


twice over the course of two weeks and > 100 runs this statement failed with ORA-01722, the rest of the time there was no problem at all =8-O

note: it's NOT the to_date, since when you provoke failure there by tweaking NLS-params, the error is 1858!

Byte64 said...

It's pretty clear that when an error is not systematic but erratic, it must depend on the data being processed or some other instantaneous condition.

I would try comparing what i get performing the query inside a loop on REQUEST_ID first, then repeating the process after removing the expression containing the NVL.

When you have an erratic condition you must do something to narrow down the range of possible causes.

Anonymous said...

Great article...explains it very well
The problem we have is we have a column of VARCHAR2 type with a mixture of strings and numbers and we cannot carry out the simplest select query
for example
where t.attribute > 10
How can i query a varchar2 against a numeric value
Thanks

Byte64 said...

Well,
the blunt reality is that keeping strings and numbers together in the same column and expecting to query these values using numerical rules is NOT a good idea.

At any rate, you need to create your own greater-than comparison function that recognizes if one parameter is a string and returns some meaningful result (NULL ?, FALSE ?).

By doing so however you are preventing oracle from using indexes on that column, which will result in poor performance for a large number of rows.

Flavio

thierrybo said...

Hi,

I understand what you explain, still I have the issue even with adding quotes in the WHERE clause.

SELECT DO.DO_PIECE+CT.CT_INTITULE
FROM F_COMPTET CT INNER JOIN F_DOCENTETE DO ON CT.CT_NUM = DO.DO_TIERS
WHERE DO.DO_PIECE='FC080920'


The weird thing : via ODBC/ACCESS it works (SQL as access write it) :


SELECT DO.DO_PIECE+CT.CT_INTITULE AS Expr1
FROM LECHENE_F_COMPTET AS CT INNER JOIN LECHENE_F_DOCENTETE AS DO ON CT.CT_NUM = DO.DO_TIERS
WHERE (((DO.DO_PIECE)='FC080920'));

Byte64 said...

Hi Thierry,
i must say i hate the ANSI syntax.

At any rate, you are using + on a string value, containing an nonconvertible string whose value is FC080920.

I don't know if the ODBC driver is translating this into a string concatenation, but may be you want to try using the double pipe operator (||) or the CONCAT function instead.

SELECT CONCAT(DO.DO_PIECE,CT.CT_INTITULE)
FROM F_COMPTET CT INNER JOIN F_DOCENTETE DO ON CT.CT_NUM = DO.DO_TIERS
WHERE DO.DO_PIECE='FC080920'

or

SELECT DO.DO_PIECE || CT.CT_INTITULE
FROM F_COMPTET CT INNER JOIN F_DOCENTETE DO ON CT.CT_NUM = DO.DO_TIERS
WHERE DO.DO_PIECE='FC080920'

unless i missed something from your comment...

Brian Tkatch said...

>i must say i hate the ANSI syntax.

Ah, a kindred spirit. :)

Byte64 said...

:-D

well, actually in comparison with the syntax of MERGE, this is a piece of art!

I could spend a thousand years writing SQL and i'd need to go back and check the syntax diagram every time to get a working statement.

thierrybo said...

Thanks,

you got it !

Byte64 said...

Excellent Thierry,
however as a rule-of-thumb for getting the best results in the future, i'd recommend you to FORGET using Oracle as if it were M$-ACCESS.

:-)

Brian Tkatch said...

>well, actually in comparison with
>the syntax of MERGE, this is a
>piece of art!
>
>I could spend a thousand years
>writing SQL and i'd need to go
>back and check the syntax diagram
>every time to get a working
>statement.


Hmm.. maybe for shortness in writing. The ANSI syntax for joins is easy to remember. Just impossible to read when there is any nesting, and not copyable into an EXISTS statement. Further, EXISTS didn't add anything that wasn't there already. And the words INNER and OUTER were a poor choice. Most people use(d) those words for context when using nested queries. Indeed, the book i learnt SQL from, used inner and outer as default aliases. It's still annoying when i don't remember someone stole my words.

And, the FROM clause adds, the WHERE clause restricts. A join clause is a restrictive clause. Therefore, it does not belong in the FROM clause and does belong in the WHERE clause. Whomever added the ANSI join clauses obviously had no appreciation for SQL.

MERGE is a different beast. It added something that wasn't there, and the clauses are separate. It is verbose though, hard to remember, and confusing to use. But, at least it added something wanted by people.

Hmm.. Fabio, if you were designing the upsert, how would you make it look?

Byte64 said...

Brian,
i am perfectly fine with the ANSI standard as it is, as long as i don't need to use it!

Hey man, it's a lot easier to criticize than to propose, you know...

;-)

Brian Tkatch said...

Flavio, first, i must apologize for misspelling your name. I simply didn't look again. I meant no disrespect.

I agree, the ANSI syntax for MERGE is acceptable. Clunky, but acceptable.

BTW, i always read your articles. I just rarely find reason to comment. Keep up the good work, please.

Byte64 said...

Brian,
don't worry, the only way to get me really pissed off is if you call me "Silvio" :-D

Don't worry, I'll be back later with more stuff, it's just a matter of putting things together, which unfortunately takes quite some time!

Take care
Flavio

JF said...

I've had this error caused by a stupid typo in a constraint:

f8019 char(1) check (8019 in ('Y','N'))

The column name 'f8019' contains a number. Using '8019' by mistake, instead of f8019 changed the logic of the constraint and caused the error.

So check your constraints too..

Sudhir Bhilar said...

You just need to trim the variable and no need to remove alphabets from table,
select ...
from ....
where trim(column_name)='1';

Byte64 said...

May be, but at any rate, that is a workaround that assumes you are fully aware of the nature of your data, which is exactly the opposite situation I was originally describing.
Another problem of this workaround is in that it will cause a full table scan unless we create a function index on that column, so if the table is really big, the workaround may turn a simple query into a rogue query taking minutes to execute.

Suresh B A said...

incase it cannot be ensured that no char data goes into the table how to write a safe SQL query to avoid this CAST error?

Byte64 said...

it's easy Suresh, do not compare the column containing the string with a numeric value, but with a string value.
Problems start if you expect to perform some numeric comparison based on conditions like greater than or even equality comparison involving non integer values.
If you compare '1'='1' it's ok, if you compare 'A'='1' it's ok, if you compare 'A'>'1' or '100.10' = '100,10' linguistic sort rules and number formats for the oracle session performing the query will kick in and the result might not be what you expect.

Anonymous said...

normally you shouldn't use numbers in your select statement if you query strings / chars.

so - you statement should be

select * from test_table
where col_a = '1';

because col_a is varchar2(10)! It's typical _ok_ that the query fells on it face, because you didn't query exactly. I saw this kind of "let the databse decide which datatype i mean" - queries alot and most times the user thought .. "oh - thats a bug .." - but the bug sits in front of the screen.

Simple rule - if your querycolum is a string/char based datatype - than you'll have to query it with ''!

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles