Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722: invalid number
ORA-01722: invalid number [message #431888] Thu, 19 November 2009 18:33 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
create  table tab_varc(user_data_15 varchar2(15))

create table tab_wf (loan_num number)

insert into tab_wf values (2)
/
insert into tab_varc values ('2')

/
insert into tab_varc values ('#')
/
commit
/


select a.USER_DATA_15
from tab_varc a
where a.USER_DATA_15 in (select loan_num from tab_wf) 


when i give the above select, i get ORA-01722 error, the data
in tab_varc is bad, and it has millions of rows, so I do not know
how to find out the bad data

is there a way to make the above query work?

i tried this

select a.USER_DATA_15
from tab_varc a
where to_number(a.USER_DATA_15) in (select loan_num from tab_wf) 


it did not work
Re: ORA-01722: invalid number [message #431890 is a reply to message #431888] Thu, 19 November 2009 18:43 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
its data issue, fixed now
Re: ORA-01722: invalid number [message #431902 is a reply to message #431888] Thu, 19 November 2009 23:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
this example is interesting from a historical perspective. There was a time when the behavior you get for implicit datatype conversion was different for the same release but on different O/Ses.

Consider this:

select * from dual where 'B' < 1;

There are two ways to handle this:

1) convert 1 to a character string and do a string compare of 'B' < '1'.
2) convert B to a number and compare B (as a number) < 1.

#1 will succeed and the compare will be false.
#2 will raise invalid_number because B does not convert to a number.

As a bit of history, a decade ago the same version of Oracle on two different OSes gave different results. On one OS you would see behavior #1, on a different OS you would see behavior #2. One would fail, the other would succeed. Now this might not seem like a big deal, until you consider this variation of the problem:

select * from dual where '10' < 2;

Just as before there are two ways to handle this:

1) convert '10' to a number and compare 10 < 2
2) convert 2 to a string and compare '10' < '2'

In this case both variations would succeed, but each gives a different results. #1 is false, #2 is true. So depending upon which OS you had, you would get a different answer.

Ah the old days were such fun. Port your code to another OS only to see it give an unexpected result. But all that is gone these days. Oracle has cleaned up its act in most places regarding non-OS specific code. So ends the history commentary and so it is made obvious why you should always use explicit type conversion.

Kevin

[Updated on: Thu, 19 November 2009 23:21]

Report message to a moderator

Re: ORA-01722: invalid number [message #431957 is a reply to message #431888] Fri, 20 November 2009 04:10 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could change the query like this:
select a.USER_DATA_15
from tab_varc a
where a.USER_DATA_15 in (select to_char(loan_num) from tab_wf) 
Previous Topic: ora-32960
Next Topic: Calulate the Eaxct Hours, Minutes second
Goto Forum:
  


Current Time: Mon Sep 26 17:56:09 CDT 2016

Total time taken to generate the page: 0.11384 seconds