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