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 <mailto:kibeha@xxxxxxxxx>
@kibeha
On Fri, Jan 30, 2015 at 12:43 PM, Jure Bratina
<jure.bratina@xxxxxxxxx <mailto: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 <mailto: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
