Re: Numeric Format

ricardo.suyon_at_consultant.volvo.com (Ricardo Suyon) wrote in message news:<6849ad5c.0109051441.1082bccc_at_posting.google.com>...
> Hi.
>
> I have an Oracle Server 7.3.4.
> When i run this script i get this result:
>
> SQLWKS> select to_number('0.125') + to_number('0.538') from dual
> 2>
> TO_NUMBER(
> ----------
> .663
> 1 row selected.
>
> I made an export from this database to a Oracle Server 8.0.4.When I
> execute the same script, I get this error message:
>
> SQLWKS> select to_number('0.125') + to_number('0.538') from dual
> 2>
> TO_NUMBER(
> ----------
> ORA-01722: invalid number
>
> All the numeric data has another numeric format:
>
> 7.3.4 8.0.4
> ======== =======
> 10235.88 1023588
> 56986.95 5698695
> 96535.45 9653545
> 12354.96 1235496
> 65842.18 6584218
> ...
>
> At parameters in both servers
>
> nls_currency
> nls_date_format
> nls_iso_currency
> nls_language AMERICAN
> nls_numeric_characters
> nls_sort
> nls_territory
>
> After I try with a full database export/import and the error remains.
>
> Can you tell me where is the error.
>
> Thanks in advance.
>
> Ricardo Suyon

It looks like you hit a bug. If you have support check Metalink as I remember reading a couple of threads and support listed a couple of bugs related to ORA-01722.

Under version 8.1.7
UT1> select 0.125 + 0.538 from dual;

0.125+0.538

       .663

UT1> select to_number('0.125') + to_number('0.538') from dual;

TO_NUMBER('0.125')+TO_NUMBER('0.538')

                                 .663

• Mark D Powell --