Re: ORA-01722: invalid number
Date: Fri, 30 Jan 2015 12:44:20 +0100
Message-ID: <CA+S=qd3-_gLUc9SUe8WVhd14ONcmSmgJVwKeD1q8Q9W3XC-1RA_at_mail.gmail.com>
Hi, Jose
Your DEFAULT values for various of your NUMBER columns are STRING values.
Hence an implicit conversion of the DEFAULT values happens when you do
INSERT.
The implicit conversion will then depend upon your session NLS settings.
Observe a little test here:
SQL> alter session set nls_numeric_characters='.,' 2 /
Session altered.
SQL> create table t1 (
2 col1 number not null 3 , col2 number default '0.0'
4 )
5 /
Table created.
SQL> insert into t1 (col1) values (1)
2 /
1 row created.
SQL> alter session set nls_numeric_characters=',.' 2 /
Session altered.
SQL> insert into t1 (col1) values (1)
2 /
insert into t1 (col1) values (1)
*
ERROR at line 1:
ORA-01722: invalid number
When you (or another) did CREATE TABLE, that was in a session that used decimal *point* (like for example NLS_TERRITORY=AMERICA.) Therefore the CREATE TABLE succeeded, because '0.0' can be correctly converted to number when using decimal *point*.
When you did your INSERT, that was in a session that used decimal *comma* (like for example NLS_TERRITORY=<many European countries>.) '0.0' cannot be converted to a number in a session using decimal *comma*.
The fix is to create your table using a NUMBER default rather than a STRING default:
create table t1 (
col1 number not null , col2 number default 0.0
)
/
(Observe there are no quotes around 0.0)
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha
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:
>> default
> name | type | length| nullable|
> --------------------------+ ---------------------------+ ------+ --------+
> ------------------
> 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-lReceived on Fri Jan 30 2015 - 12:44:20 CET