Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: ora-01722 invalid number
"Christian" <luetke.entrup_at_gmx.de> writes:
> Hello everybody,
>
> hope anybody can help me.
>
> I got the this invalid number exception when I tried to store a
> floating-point number with a comma as decimal separator
> in my Java-App.
> My customer wishes to use German numeric character settings, which is a
> comma as decimal separator and '.' for the thousands.
> I know that I can chance the NLS_NUMERIC_CHARACTERS setting, but if I set
> it to '.,', which is exactly what I need,
> I can store the number with a comma, but when I retrieve the same number
> back from the DB, it is shown with a '.' instead of the comma,
> and the next try to store this number back fails with the exception above.
>
> example:
> a java textfield with the value '9,23' is saved in the Oracle-DB correctly.
> retrieving this value back into the same textfield appears as '9.23'
> saving again fails with ORA-01722 until I change the '.' back to comma.
>
> I'm using Oracle 9.2.0.1.0
> NLS_LANG-settings : GERMAN_GERMANY.WE8ISO8859P1
> additional settings: NLS_DATE_FORMAT='DD.MM.YYYY'";
> NLS_NUMERIC_CHARACTERS='.,'";
>
> it seems to be strange that the NLS-setting for germany is not
> sufficient for my purposes, because it's only the common german
> numeric setting I have to use. Maybe a bug in the Oracle-NLS??
>
In this case I would use "to_char(sal, '99G999D99')" and access the string with ResultSet.getString. If you need the string as a Float then use ResultSet.getFloat without a conversion. As far as I understand '99G999D99' uses the nls parameter NLS_ISO_CURRENCY for conversion.
,----[ NLS ]
| PARAMETER VALUE | ------------------------------ ---------------------------------------- | NLS_LANGUAGE GERMAN | NLS_TERRITORY GERMANY | NLS_CURRENCY ¿ | NLS_ISO_CURRENCY GERMANY | NLS_NUMERIC_CHARACTERS ,. | NLS_CALENDAR GREGORIAN | NLS_DATE_FORMAT DD.MM.RR | NLS_DATE_LANGUAGE GERMAN | NLS_SORT BINARY | NLS_TIME_FORMAT HH24:MI:SSXFF | NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF | NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR | NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR | NLS_DUAL_CURRENCY ¿ | NLS_COMP BINARY | NLS_LENGTH_SEMANTICS BYTE | NLS_NCHAR_CONV_EXCP FALSE
,----[ Query ]
| "select ename, to_char(sal, '99G999D99') from emp where ename like ?"
`----
,----[ Result ]
| ALLEN 1.600,00
| ADAMS 1.100,00
`----
Harald Received on Tue Aug 26 2003 - 06:37:32 CDT
![]() |
![]() |