Re: FW: ORA-01722: invalid number
Date: Wed, 04 Feb 2015 14:43:49 +0100
Message-ID: <54D22215.1010903_at_sferacarta.com>
Thanks for your answer, Mark.
In the meantime I was arrived at a conclusion to be drawn similar to yours and in fact I replaced all those '0.0' (*) with 0 using
alter table "table_name"modify "column_name" number default 0 and now it works.
Thanks again for your help.
j
(*) the schema was created automaticaly by sqlalchemy with DefaultClause which defines defaults as strings.
On 02/04/2015 10:36 AM, Mark W. Farnham wrote:
>
> Sorry for the delay. Something bounced.
>
> *From:*Mark W. Farnham [mailto:mwf_at_rsiz.com]
> *Sent:* Wednesday, February 04, 2015 4:35 AM
> *To:* 'jose.soares_at_sferacarta.com'; 'oracle-l_at_freelists.org'
> *Subject:* RE: ORA-01722: invalid number
>
> Having default values that require conversions would seem to be the
> root cause.
>
> Consider:
>
> SQL> r
>
> 1 select
> column_name,data_type,data_type_mod,data_type_owner,data_length,data_precision,data_scale,default_length,data_default
>
> 2* from dba_tab_columns where owner = 'MWF' and table_name = 'JUNK_NLS2'
>
> COLUMN_NAME DATA_TYPE DAT DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION
> DATA_SCALE DEFAULT_LENGTH DATA_DEFAULT
>
> ------------ ---------- --- --------------- ----------- --------------
> ---------- -------------- --------------------
>
> ID NUMBER ~ ~ 22 ~ ~
> ~ ~
>
> ID2 NUMBER ~ ~ 22 ~
> ~ 5 '0.0'
>
> SQL> c/2//
>
> 2* from dba_tab_columns where owner = 'MWF' and table_name = 'JUNK_NLS'
>
> SQL> r
>
> 1 select
> column_name,data_type,data_type_mod,data_type_owner,data_length,data_precision,data_scale,default_length,data_default
>
> 2* from dba_tab_columns where owner = 'MWF' and table_name = 'JUNK_NLS'
>
> COLUMN_NAME DATA_TYPE DAT DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION
> DATA_SCALE DEFAULT_LENGTH DATA_DEFAULT
>
> ------------ ---------- --- --------------- ----------- --------------
> ---------- -------------- --------------------
>
> ID NUMBER ~ ~ 22 ~ ~
> ~ ~
>
> ID2 NUMBER ~ ~ 22 ~
> ~ 3 0.0
>
> So if you see (from Jure below)
>
> create table test(id number, id2 number default '0.0');
>
> that would be as in JUNK_NLS2. (no pejorative there, JUNK just means
> stuff I can throw away)
>
> To avoid these problems define your defaults like JUNK_NLS:
>
> create table junk_nls (id number, id2 number default 0.0);
>
> so that the resulting table does not need data conversion upon the
> insertion of each row. (And yes, I think Kim has a point that Oracle
> should at least raise a warning (probably at create time as well as
> insert time) that your default value require conversion. And failing
> to name the offending column is on a par with cable customer service
> instructions.
>
> So I’d fix the table:
>
> SQL> alter table junk_nls2 modify id2 default 0;
>
> Table altered.
>
> SQL> select
> column_name,data_type,data_type_mod,data_type_owner,data_length,data_precision,data_scale,default_length,data_default
>
> 2 from dba_tab_columns where owner = 'MWF' and table_name =
> 'JUNK_NLS2';
>
> COLUMN_NAME DATA_TYPE DAT DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION
> DATA_SCALE DEFAULT_LENGTH DATA_DEFAULT
>
> ------------ ---------- --- --------------- ----------- --------------
> ---------- -------------- --------------------
>
> ID NUMBER ~ ~ 22 ~
> ~ ~ ~
>
> ID2 NUMBER ~ ~ 22 ~
> ~ 1 0
>
> for example would change the table Jure offers so no conversion is
> required.
>
> Unless your dba has some reason for storing a numeric default as a
> string (I’d be curious as to that answer) I do not understand wanting
> the default value to require conversion on insert or taking up more
> space than it needs to in the dictionary.
>
> Anyway, then it should not matter what your NLS parameters are
> anywhere in the stack. It is possible that your dba will say the table
> is too big to modify and you should be ready to inform the dba this
> operation on default values scales with the number of columns you need
> to change, not the number of rows in the table.
>
> I’m also curious exactly what the source of your “schema” extract was
> below.
>
> Finally, a useful test is to create a test table using your current
> stack having all the columns with defaults populated from the
> problematic table typing the default values exactly as you have them.
> If that fails on some conversion the error returned in sqlplus will
> have an asterisk marking the first problem hit.
>
> Along the lines of Kim’s suggestions a
>
> testparse create table xyz (id number);
>
> command that didn’t stop at the first error and continued (up to a
> defined limit because sometimes things south fast after the first
> error) would be nice. Do not hold your breath. That request is from
> about 1984.
>
> Good luck,
>
> mwf
>
> *From:*oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Jose Soares
> *Sent:* Wednesday, February 04, 2015 3:04 AM
> *To:* oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> *Subject:* Re: ORA-01722: invalid number
>
> 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 <mailto:kibeha_at_gmail.com>
>
> _at_kibeha
>
> On Fri, Jan 30, 2015 at 12:43 PM, Jure Bratina
> <jure.bratina_at_gmail.com <mailto: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 <mailto: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-lReceived on Wed Feb 04 2015 - 14:43:49 CET