Ora-2290 On Insert Into Table With Temporal Validity
(Doc ID 1632859.1)
Last updated on FEBRUARY 26, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.1 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Insert into table with temporal validity raises ORA-2290
SQL> insert into presidents1 values (
2 'John F. Kennedy' , date '1917-05-29', date '1963-11-22'
3 )
4 /
insert into presidents1 values (
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.ALIVEEB452E) violated
Hidden virtual column ALIVE has default a negative integer.
SQL> select tc.column_name
2 , tc.data_type
3 , tc.column_id
4 , tc.data_default
5 , tc.hidden_column
6 , tc.virtual_column
7 from user_tab_cols tc
8 where tc.table_name = 'PRESIDENTS1'
9 order by tc.column_id
10 /
COLUMN_NAME DATA_TYPE COLUMN_ID DATA_DEFAULT HID VIR
-------------------- ---------- ---------- -------------------- --- ---
NAME VARCHAR2 1 NO NO
BIRTH DATE 2 NO NO
DEATH DATE 3 NO NO
ALIVE NUMBER -1645525714 YES YES
Constraint is auto-created requiring ALIVE to be greater than zero?
SQL>
SQL> select uc.constraint_name
2 , uc.constraint_type
3 , uc.search_condition
4 from user_constraints uc
5 where uc.table_name = 'PRESIDENTS1'
6 /
CONSTRAINT_NAME C SEARCH_CONDITION
-------------------- - ---------------------------------------------
ALIVEEB452E C (BIRTH < DEATH) and (ALIVE > 0)
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |