Re: ORA-01722: invalid number
- From: Kim Berg Hansen <kibeha@xxxxxxxxx>
- To: jure.bratina@xxxxxxxxx
- Date: Fri, 30 Jan 2015 12:58:44 +0100
(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:
