Re: ORA-01722: invalid number

From: Jose Soares <jose.soares_at_sferacarta.com>
Date: Wed, 04 Feb 2015 09:03:41 +0100
Message-ID: <54D1D25D.2060508_at_sferacarta.com>



Hi all,

I asked the db administer to set nls_numeric_characters but he says me that it is already OK
and sent me this list where infact it seems correct.

Should it be ",." or ".," in my case?

select * from nls_database_parameters;
parameter | value

-----------------------+ ----------------------------
NLS_LANGUAGE           | AMERICAN
NLS_TERRITORY          | AMERICA
NLS_CURRENCY           | $
NLS_ISO_CURRENCY       | AMERICA
NLS_NUMERIC_CHARACTERS | .,
NLS_CHARACTERSET       | WE8ISO8859P15
NLS_CALENDAR           | GREGORIAN
NLS_DATE_FORMAT        | DD-MON-RR
NLS_DATE_LANGUAGE      | AMERICAN
NLS_SORT               | BINARY
NLS_TIME_FORMAT        | HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT   | DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT     | HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT| DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY      | $
NLS_COMP               | BINARY
NLS_LENGTH_SEMANTICS   | BYTE
NLS_NCHAR_CONV_EXCP    | FALSE
NLS_NCHAR_CHARACTERSET | AL16UTF16
NLS_RDBMS_VERSION      | 10.2.0.5.0

(20 rows)

He said me also, that probably it depends on the client language.

j

On 01/30/2015 02:38 PM, Jose Soares wrote:

> On 01/30/2015 12:58 PM, Kim Berg Hansen wrote:

>> (Practically simultaneous thinking, Jure ;-)
>>
>>
>> A discussion question to everybody:
>>
>> The docs on CREATE TABLE state as follows:
>>
>> DEFAULT
>>
>> The |DEFAULT| clause lets you specify a value to be assigned to
>> the column if a subsequent |INSERT| statement omits a value for
>> the column. The data type of the expression must match the data
>> type specified for the column. The column must also be large
>> enough to hold this expression.
>>
>>
>> (Highlighting by me.)
>>
>> * Would you consider this a doc bug? Should it have said "the
>> expression must match the data type or be able to be implicitly
>> converted"?
>>
>> * Can you think of *any *use case where it makes sense that the
>> expression does *not *match data type but "only" can be
>> implicitly converted?
>>
>> * Would you expect Oracle to raise an exception when you try to use
>> a string value as default for a number column?
>>
>> * Would you consider it a good Request For Enhancement to ask
>> Oracle to raise such an exception when datatype in DEFAULT clause
>> does not match column datatype properly?
>>
>>
>> Personally I can't think of a valid use case, so my vote is that I
>> would like Oracle to raise exception at CREATE TABLE if datatypes of
>> DEFAULT and column does not match.
>
> This error raises when one try to create a table using a column 
> default that doesn't match the definition of:
> NLS_NUMERIC_CHARACTERS  | .,
>
> but, the error even occurs if you after have created the table with 
> the right value for NLS_NUMERIC_CHARACTERS ,
> you decide to change it later, as in my case.
>
>
>

>> But I may have missed some strange use case that my fantasy is not
>> good enough to imagine ;-)
>>
>> Your opinions?
>
> The error message is not clear.
>
> At least I expected something like this:
>
> ORA-1722: invalid value on column "column_name"
>
>
>

>>
>>
>> Regards
>>
>>
>> Kim Berg Hansen
>>
>> http://dspsd.blogspot.com
>> kibeha_at_gmail.com <mailto:kibeha_at_gmail.com>
>> _at_kibeha
>>
>>
>>
>> On Fri, Jan 30, 2015 at 12:43 PM, Jure Bratina
>> <jure.bratina_at_gmail.com <mailto:jure.bratina_at_gmail.com>> wrote:
>>
>> Hi,
>>
>> Do you have any triggers defined on the table? If not, maybe the
>> default value '0.0' on the NUMBER type columns might be the
>> problem e.g.:
>>
>> SQL> select value
>> 2 from nls_session_parameters
>> 3 where parameter = 'NLS_NUMERIC_CHARACTERS';
>>
>> VALUE
>> ----------
>> .,
>>
>> SQL> create table test(id number, id2 number default '0.0');
>>
>> Table created.
>>
>> SQL> insert into test (id) values (1);
>>
>> 1 row created.
>>
>> SQL> alter session set NLS_NUMERIC_CHARACTERS=',.';
>>
>> Session altered.
>>
>> SQL> insert into test (id) values (1);
>> insert into test (id) values (1)
>> *
>> ERROR at line 1:
>> ORA-01722: invalid number
>>
>>
>> SQL>
>>
>> Regards,
>> Jure Bratina
>>
>> On Fri, Jan 30, 2015 at 12:18 PM, Jose Soares
>> <jose.soares_at_sferacarta.com <mailto:jose.soares_at_sferacarta.com>>
>> wrote:
>>
>> Hi all,
>> I have a strange error that I can't understand
>> when I try to insert a row into the table below, I get this
>> error:
>>
>> (DatabaseError) ORA-01722: invalid number
>> "INSERT INTO fattura_master (sezionale, anno, numero,
>> data_emissione) VALUES ('04', 2015, 9999, current_date)"
>>
>>
>> here's the table schema:
>>
>> name | type | length|
>> nullable| default
>> --------------------------+ ---------------------------+
>> ------+ --------+ ------------------
>> tipo_documento | nvarchar2 | 1 | Y
>> | NULL
>> sezionale | nvarchar2 | 3 | N
>> | NULL
>> anno | number | 0 | N
>> | NULL
>> numero | number | 0 | N
>> | NULL
>> data_emissione | date | 0 | N
>> | NULL
>> data_competenza | date | 0 | Y
>> | NULL
>> tipo_pagamento | nvarchar2 | 5 | Y
>> | NULL
>> data_pagamento | date | 0 | Y
>> | NULL
>> importo_pagato | number | 0 | Y
>> | '0.0'
>> bollo | number | 0 | Y
>> | '0.0'
>> note | nvarchar2 | 200 | Y
>> | NULL
>> ced | number | 0 | Y
>> | NULL
>> id_anagrafica_sede_fiscale| number | 0 | Y
>> | NULL
>> intestazione | nvarchar2 | 100 | Y
>> | NULL
>> indirizzo | nvarchar2 | 100 | Y
>> | NULL
>> cap | nvarchar2 | 5 | Y
>> | NULL
>> comune | nvarchar2 | 100 | Y
>> | NULL
>> provincia | nvarchar2 | 2 | Y
>> | NULL
>> codice_fiscale | nvarchar2 | 16 | Y
>> | NULL
>> partita_iva | nvarchar2 | 11 | Y
>> | NULL
>> id_distretto | number | 0 | Y
>> | NULL
>> istat | nvarchar2 | 6 | Y
>> | NULL
>> iva | number | 0 | Y
>> | '0.0'
>> aliquota_iva | number | 0 | Y
>> | NULL
>> imponibile | number | 0 | Y
>> | '0.0'
>> enpav_iva | number | 0 | Y
>> | '0.0'
>> fuori_campo | number | 0 | Y
>> | '0.0'
>> enpav_fc | number | 0 | Y
>> | '0.0'
>> rif_nr_na | number | 0 | Y
>> | NULL
>> rif_aa_na | number | 0 | Y
>> | NULL
>> codice_cliente | nvarchar2 | 15 | Y
>> | NULL
>> codice_attivita | nvarchar2 | 10 | Y
>> | NULL
>> ts_ultima_modifica | timestamp(6) with time zone| 0
>> | Y | CURRENT_TIMESTAMP
>> id_operatore | number | 0 | Y
>> | NULL
>> (34 rows)
>>
>> index_type | index_name | uniqueness| column_name|
>> column_position
>> -----------+ -------------+ ----------+ -----------+
>> ---------------
>> primary key| sys_c00116651| unique | sezionale | 1
>> primary key| sys_c00116651| unique | anno | 2
>> primary key| sys_c00116651| unique | numero | 3
>> --------------------------------------------------------------------------------------------------------------------
>> thanks for any help.
>>
>> j
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>>

>
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 04 2015 - 09:03:41 CET

Original text of this message