Error message-ORA-01722: invalid number 2

[TekMem]

I am using oracle 9.2.0.1

I have very simple select statement which runs on another computers but one of the comp it gives error message ORA-01722: invalid number

 select  to_number(varchar_255_27) 
from up where  varchar_255_27 is not null and varchar_255_27 <> '0'

 

[ddiamond]

Check your data. Is varchar_255_27 always numeric?

 

[carp]

Well, apparently there is at least one value that won't convert to a number on this one database. To find it, try something like the following:

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
   l_num NUMBER;
BEGIN
   FOR i IN (select  varchar_255_27 
               from up 
              where  varchar_255_27 is not null 
                and varchar_255_27 <> '0') LOOP
   BEGIN
      l_num := TO_NUMBER(i.varchar_255_27);
   EXCEPTION
      WHEN OTHERS THEN 
         dbms_output.put_line('PROBLEM WITH: '||i.varchar_255_27);
   END:
   END LOOP;
END;
/

 

[TekMem]

Thanks ddiamond

If problem with data other computers should have problem too.
I have tried uninstalling and reinstalling oracle but does not work.

I do not think something to do with datatype it is nvarchar2(255), if it is...it should effect other computers as well.

select varchar_255_27
from up where varchar_255_27 is not null and varchar_255_27 <> '0'
Result is fine

select varchar_255_27
from up where varchar_255_27 is not null and varchar_255_27 <> 0
ORA-01722: invalid number

 

[TekMem]

Carp,

I get

PROBLEM WITH: .5
PROBLEM WITH: .25
PROBLEM WITH: 0.75
PROBLEM WITH: 0.75
PROBLEM WITH: 0.75
PROBLEM WITH: 1.25
PROBLEM WITH: 1.25
PROBLEM WITH: 2.5
PROBLEM WITH: 0.5

 

[TekMem]

How should I deal with this problem. Need help. Want to add all those numbers.

Thanks

 

[SantaMufasa]

TekMem (and Carp and DDiamond),

There is definitely something flakey going on with TekMem's installation:

I just attempted to recreate the problem on my (WinXP 9.2.0.6) Oracle instance, and I cannot do so. (I even tried to "confuse" Oracle with both leading and trailing blanks around the numeric values, but it still did not throw any errors.) Here is my session:

create table up (varchar_255_27 varchar2(10))
/
insert into up values (' .5 ');
insert into up values ('  .25 ');
insert into up values ('  0.75 ');
insert into up values ('  0.75 ');
insert into up values ('  0.75 ');
insert into up values ('  1.25 ');
insert into up values ('  1.25 ');
insert into up values ('  2.5 ');
insert into up values ('  0.5 ');

DECLARE
   l_num NUMBER;
BEGIN
   FOR i IN (select  varchar_255_27
               from up
              where  varchar_255_27 is not null
                and varchar_255_27 <> '0') LOOP
   BEGIN
      l_num := TO_NUMBER(i.varchar_255_27);
   EXCEPTION
      WHEN OTHERS THEN
         dbms_output.put_line('PROBLEM WITH: '||i.varchar_255_27);
   END;
   END LOOP;
END;
/

PL/SQL procedure successfully completed.

So there is something different between TekMem's installation and mine.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[carp]

OK, let's take one step under the hood:

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
   l_num NUMBER;
BEGIN
   FOR i IN (select  varchar_255_27, dump(varchar_255_27) hood
               from up 
              where  varchar_255_27 is not null 
                and varchar_255_27 <> '0') LOOP
   BEGIN
      l_num := TO_NUMBER(i.varchar_255_27);
   EXCEPTION
      WHEN OTHERS THEN 
         dbms_output.put_line('PROBLEM WITH: '||i.varchar_255_27||' => '||i.hood);
   END;
   END LOOP;
END;
/

This will allow us to confirm ALL byte values in the column.

 

[TekMem]

This time I got

PROBLEM WITH: .5 => Typ=1 Len=2: 46,53
PROBLEM WITH: .25 => Typ=1 Len=3: 46,50,53
PROBLEM WITH: 0.75 => Typ=1 Len=4: 48,46,55,53
PROBLEM WITH: 0.75 => Typ=1 Len=4: 48,46,55,53
PROBLEM WITH: 0.75 => Typ=1 Len=4: 48,46,55,53
PROBLEM WITH: 1.25 => Typ=1 Len=4: 49,46,50,53
PROBLEM WITH: 1.25 => Typ=1 Len=4: 49,46,50,53
PROBLEM WITH: 2.5 => Typ=1 Len=3: 50,46,53
PROBLEM WITH: 0.5 => Typ=1 Len=3: 48,46,53

Is there anything to do with windowXP? any setting?

Thanks

 

[carp]

Well, I'M stumped. I loaded these values onto an XP machine, got the same DUMP results, and was able to TO_NUMBER all of them successfully. This makes no sense to me.
What character set is your database using?

 

[hoinz]

Hello all,

a striking thing is the decimal point in all the offending lines.
Maybe the database will need a decimal comma instead?

TekMem,

to verify, please could you run these two selects:
select to_number('0.123') from dual;
select to_number('0,123') from dual;

hope this helps

 

[TekMem]

Hoinz,

SQL> select to_number('0.123') from dual;
select to_number('0.123') from dual
*
ERROR at line 1:
ORA-01722: invalid number


SQL> select to_number('0,123') from dual;

TO_NUMBER('0,123')
------------------
,123

Yes decimal is the problem. So what should I do?

only one machine has problem rest of them are ok.

 

[hoinz]

So it seems your NLS settings (NLS = National Language Support, if I remember correctly) differ on your machines.
The cleanest solution probably would be to make NLS settings the same.
But of course you could also replace all the '.' by ',' ...

For a first guess, compare the result for this query on your machines:
select value from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS'

regards

 

[carp]

Hoinz -
Excellent catch! Please have a star on me!

 

[hoinz]

carp,
thanks!

 

[TekMem]

Thanks to everybody for the valuable input.

Hoiz, I owe you a golden star!

SQL> select value from nls_session_parameters
2 where parameter = 'NLS_NUMERIC_CHARACTERS';

VALUE
--------------------------------------------------
,

1. How can I change back to period(decimal).

Even my date format is different too.
SQL> select value from nls_session_parameters
2 where parameter = 'NLS_DATE_FORMAT';

VALUE
--------------------
RR-MM-DD

2. Is it to do with oracle installation which i did not take care.

 

[hoinz]

TekMem,

so it is because of your nls_session_parameters; this word suggests that it is not a database setting, but a client setting, i.e. it is chosen when a session starts; it comes from the environment of the operating system user who is running the queries.
All that is to be done now largely depends on your operating system. You didn't tell us so far.
And depending on your operating system, I may or may not be able to help you further.
And by the way, it is evening here, and I have to leave now. I trust that other Tek-Tips members will jump in.

regards

 

[TekMem]

Thanks Hoinz!

I had a doubt about my OS setting...initially I had asked is it to do with my XP settings.

I have to figureout somthing which is good for both the type of the settings. So that my program can run on both.

Good night hoinz...Anyway still good morning for me.

I will appreciate if could get any clue here to deal with this type of situation.