Re: ORA-01722: invalid number

From: Kim Berg Hansen <kibeha_at_gmail.com>
Date: Wed, 4 Feb 2015 09:28:30 +0100
Message-ID: <CA+S=qd0hBVuQ4+6zdtN8+VUf-aEECy9+hZ14-FO4QBKp=XjKTg_at_mail.gmail.com>

Yes, it depends on client NLS settings. Try:

select * from nls_*session*_parameters;

But as you have no control over what NLS settings various clients have that access your database, you must not have *implicit* conversion happening in your database. The implicit conversion will use whatever NLS settings the client has, and you have no control.

So you must not have STRING defaults for your NUMBER columns. It *will *go wrong - it *will* be a bug in your code.

Do *not* use this:

create table t1 (

   col1     number   not null
 , col2     number   default *'0.0'*

)
/

Instead use this:

create table t1 (

   col1     number   not null
 , col2     number   default *0.0*

)
/

Remove the quotes surrounding your values in the DEFAULT clauses, then you will use NUMBER in a column that is NUMBER datatype and you will have avoided implicit conversion and then it will not matter what the client NLS settings are.

(
In NLS_NUMERIC_CHARACTERS, the first character is the decimal character, the second is the group character.
'.,' is correct for NLS_TERRITORY=AMERICA, as that is decimal point and group comma.
)

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha

On Wed, Feb 4, 2015 at 9:03 AM, Jose Soares <jose.soares_at_sferacarta.com> wrote:

> 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
> _at_kibeha
>
>
>
> On Fri, Jan 30, 2015 at 12:43 PM, Jure Bratina <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
>> > 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:28:30 CET