CODEWITHSUNDEEP
sqloracle-database
ramesh
ramesh

Reputation: 300

TO_CHAR(number) Function returns ORA-01722: invalid number

Query:
Select To_Number(qty) From my_table Where Id=12345;
Output:
ORA-01722: invalid number
01722. 00000 - "invalid number"

Query: Select qty From my_table Where Id=12345;
Output: 0.00080

Query:
Select To_Number(0.00080) From Dual;
Output:
0.00080 (no error)

This is a odd situation I am facing in Oracle. Can anybody suggest why it happens? The column qty is NUMBER type. Hence it is very hard to imagine that it contains invalid number, but it happened.
I want to clarify that it happened for the specific value in the column although we have thousands of records in the same column.
Added more: The same error appears if I use TO_CHAR(qty) function. The qty column is NUMBER type not VARCHAR2. In fact we are using SUM(qty) function which showed error. Hence I went for a dissection and found this row being the culprit.

Upvotes: 1

Views: 16745

Answers (4)

Henry
Henry

Reputation: 11

I encountered the nearly same problem. And I found the mysterious number behaved differently from the normal number after dump(). For example, assuming my qty=500 (datatype: number(30,2)) , then:

select dump(qty) from my_table where Id=12345;

Typ=2 Len=3: 194,6,1

select dump(500.00) from dual;

Typ=2 Len=2: 194,6

If we know how number datatype be stored (if not, plz visit http://translate.google.com/translate?langpair=zh-CN%7Cen&hl=zh-CN&ie=UTF8&u=http%3A//www.eygle.com/archives/2005/12/how_oracle_stor.html ) , we can find that there is a tailing zero (the last extra "1" in Typ=2 Len=3: 194,6,1) in the mysterious number.

So I made a trick to eliminate the tailing zero, and it works for the problem.

select dump(trunc(qty+0.001,2)) from my_table where Id=12345;

Typ=2 Len=2: 194,6

Hope someone to explain the deep mechanism.

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191265

The only way I can see you could get the results you've shown, given that qty really is a number field, is if it holds corrupt data (which is why there has been scepticism about that assumption). I'm also assuming your client is formatting the value with a leading zero, but is not forcing the trailing zero, which wouldn't normally appear; you can of course force it with to_char(.0008, '0.00000'), but you don't appear to be doing that; still, the leading zero makes me wonder.

Anyway, to demonstrate corruption you can force an invalid value into the field via PL/SQL - don't try this with real data or a table you care about:

create table t42(qty number);

table T42 created.

declare
  n number;
begin
  dbms_stats.convert_raw_value('bf0901', n);
  insert into t42 (qty) values (n);
end;
/

anonymous block completed

select qty from t42;

       QTY
----------
    .00080 

select to_number(qty) from t42;

Error starting at line : 12 in command -
select to_number(qty) from t42
Error report -
SQL Error: ORA-01722: invalid number
01722. 00000 -  "invalid number"

Note the plain query shows the number as expected - though with a trailing zero, and no leading zero - and running it through to_number() throws ORA-01722. Apart from the leading zero, that is what you've shown.

It also fails with to_char(), as in your question title:

select to_char(qty) from t42;

Error starting at line : 13 in command -
select to_char(qty) from t42
Error report -
SQL Error: ORA-01722: invalid number

... which makes sense; your to_number() is doing an implicit conversion, so it's really to_number(to_char(qty)), and it's the implicit to_char() that actually generates the error, I think.

Your comments suggest you have a process that is loading and removing data. It would be interesting to see exactly what that is doing, and if it could be introducing corruption. This sort of effect can be achieved through OCI as the database will trust that the data it's passed is valid, as it does in the PL/SQL example above. There are bug reports suggesting imp can also cause corruption. So the details of your load process might be important, as might the exact database version and platform.

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231661

I'm assuming that qty is defined as a varchar2 in my_table-- otherwise, there would be no purpose served by calling to_number. If that assumption is correct, I'll wager that there is some other row in the table where qty has non-numeric data in it.

SQL is a set-based language so Oracle (or any other database) is perfectly free to evaluate things in whatever order it sees fit. That means that Oracle is perfectly free to evaluate the to_number(qty) expression before applying the id=12345 predicate. If Oracle happens to encounter a row where the qty value cannot be converted to a number, it will throw an error.

It is also possible that there is some non-numeric data in the particular row where id = 12345 that happens not to be displaying (control characters for example). You can check that by running the query

SELECT dump(qty, 1016) 
  FROM my_table
 WHERE id = 12345

(if you want decimal rather than hexadecimal, use 1010 as the second parameter to dump) and checking to see whether there is anything unexpected in the data.

Upvotes: 4

BWS
BWS

Reputation: 3836

try this:

Select To_Number(trim(qty)) From my_table Where Id=12345;

Upvotes: 0

Related Questions