Re: ORA-01722: invalid number

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Fri, 30 Jan 2015 17:18:13 -0500
Message-ID: <54CC0325.2020001_at_yahoo.com>

On 01/30/2015 06:18 AM, Jose Soares 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
>
>

To be exact, you can investigate by doing something like this:

SQL> alter session set events='01722 trace name errorstack forever, level 16';

Session altered.

That will produce a trace when you encounter ORA-01722. Alert log will contain the trace file:

2015-01-30 17:09:15.858000 -05:00
Errors in file /oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_22622.trc: ORA-01722: invalid number

Check the trace file to figure out the precise SQL causing the trouble.

-- 
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 30 2015 - 23:18:13 CET