Re: ORA-01722: invalid number
- From: Kim Berg Hansen <kibeha@xxxxxxxxx>
- To: jure.bratina@xxxxxxxxx
- Date: Fri, 30 Jan 2015 13:02:16 +0100
PS. Addendum to the Q list in previous mail:
- Would it be an idea that the implicit conversion was allowed at CREATE
time, but the *converted* value was stored in the data dictionary?
(Or would that become an unexpected sideeffect that could be difficult
to find for a developer?)
(Sorry I didn't think of that one while writing previous mail ;-)
On Fri, Jan 30, 2015 at 12:58 PM, Kim Berg Hansen <kibeha@xxxxxxxxx> 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.
> But I may have missed some strange use case that my fantasy is not good
> enough to imagine ;-)
>
> Your opinions?
>
>
> Regards
>
>
> Kim Berg Hansen
>
> http://dspsd.blogspot.com
> kibeha@xxxxxxxxx
> @kibeha
>
>
>
> On Fri, Jan 30, 2015 at 12:43 PM, Jure Bratina <jure.bratina@xxxxxxxxx>
> 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@xxxxxxxxxxxxxx
>> > 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
>>> --
>>> //www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>
>
Other related posts: