We help IT Professionals succeed at work.
Get Started

oracle invalid number/find actual value in a field

tancat
tancat asked
on
1,571 Views
Last Modified: 2015-03-09
I am using Oracle EBS 12.1.3, database 10g.  This 'appears' to be bad data, but I cannot figure out the problem.  

I have two records with an identical value in a varchar2(150) field (attribute1).  When I query the table for the good record and compare to_char(attribute1,'00.00') to null, I get a result because the value is not null.  When I query the table for the bad record and compare to_char(attribute1,'00.00') to null, I get ORA-01722: invalid number (the value is not null, it is the same as for the good record).

So I queried the table twice, once for each record:
select good.attribute1 good_attr
, bad.attribute1 bad_attr
from ( select * from table1 where person = 12345 ) good_attr
, ( select * from table1 where person = 98765 ) bad_attr
where good_attr.attribute1 = bad_attr.attribute1;
I got one row, both values are (or appear to be) '8.05'

When I use the to_char function in the where clause, I get the same results.

When I change the where clause to:
where to_char(good_attr.attribute1,'00.00') is not null;
I get the same results.

When I change the where clause to:
where to_char(bad_attr.attribute1) is not null;
I get the same results.

However, when I change the where clause to:
where to_char(bad_attr.attribute1,'00.00') is not null;
I get the invalid number error.

How can the values appear to be identical but one throws an error and the other does not?

Then I removed the value (in test) from the bad attribute1 field, ran the same query, and got the same error, even though there is nothing in that field (theoretically).

If I use to_char(attribute,'00.00') for any other attribute field on the bad record, no error.  

This is the only record that throws this error - all of the other records are fine.

I also tried using dump:
select dump(attribute1) --as well as with, 8, 10, 16, and 17
from table1
where person in (12345,98765);
and both fields appear to be identical - no hidden characters

When I use utl_match.edit_distance on the fields, the result is 0.

Any ideas on why I would get these results for observably a valid value in this field for just one record?  Am I chasing the wrong suspect?
Comment
Watch Question
Oracle Developer
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE