Re: FW: ORA-01722: invalid number
- From: MARK BRINSMEAD <mark.brinsmead@xxxxxxxxx>
- To: jose.soares@xxxxxxxxxxxxxx
- Date: Wed, 4 Feb 2015 12:02:32 -0500
Excellent. Good to hear that it works.
Not using explicit TYPE CONVERSION in the table definition is definitely
the "correct" solution to this problem.
As for the NLS settings that were causing you grief, let's just clarify
again... The settings at the *database* level are (usually) irrelevant.
It is (often) the settings that apply at the *session* level that will
cause you this sort of pain.
But of course, the pain can be permanently eliminated by removing the
explicit conversions from your table definition, exactly as you have done.
:-)
On Wed, Feb 4, 2015 at 8:43 AM, Jose Soares <jose.soares@xxxxxxxxxxxxxx>
wrote:
> 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@xxxxxxxx <mwf@xxxxxxxx>]
> *Sent:* Wednesday, February 04, 2015 4:35 AM
> *To:* 'jose.soares@xxxxxxxxxxxxxx'; 'oracle-l@xxxxxxxxxxxxx'
> *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@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx>] *On
> Behalf Of *Jose Soares
> *Sent:* Wednesday, February 04, 2015 3:04 AM
> *To:* oracle-l@xxxxxxxxxxxxx
> *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@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:
