ank5
asked on
Update date field
I have a table in which I have the following fields
COLUMN_NAME DATA_TYPE NULLABLE DATA_DEFAULT
R_MODIFY_DATE DATE No (null)
R_MODIFIER VARCHAR2(32 BYTE) No (null)
R_LOCK_OWNER VARCHAR2(32 BYTE) No (null)
R_LOCK_DATE DATE No (null)
R_LOCK_MACHINE VARCHAR2(80 BYTE) No (null)
R_OBJECT_ID VARCHAR2(16 BYTE) No
I need to update a particular to have all blank or null values. I am executing the below query
update dm_sysobject_s
set r_lock_owner=' ',
r_lock_date=null,
r_lock_machine=' '
where r_object_id='3d00c0ba81cbd 5c6'
It gives me an error message
ORA-01407: cannot update ("EDMSDEV"."DM_SYSOBJECT_S "."R_LOCK_ DATE") to NULL
Can someone please let me know how to set the R_LOCK_DATE to blank or null.
COLUMN_NAME DATA_TYPE NULLABLE DATA_DEFAULT
R_MODIFY_DATE DATE No (null)
R_MODIFIER VARCHAR2(32 BYTE) No (null)
R_LOCK_OWNER VARCHAR2(32 BYTE) No (null)
R_LOCK_DATE DATE No (null)
R_LOCK_MACHINE VARCHAR2(80 BYTE) No (null)
R_OBJECT_ID VARCHAR2(16 BYTE) No
I need to update a particular to have all blank or null values. I am executing the below query
update dm_sysobject_s
set r_lock_owner=' ',
r_lock_date=null,
r_lock_machine=' '
where r_object_id='3d00c0ba81cbd
It gives me an error message
ORA-01407: cannot update ("EDMSDEV"."DM_SYSOBJECT_S
Can someone please let me know how to set the R_LOCK_DATE to blank or null.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or just alter the column definition (= removing the contraint)
ALTER TABLE DM_SYSOBJECT_S MODIFY R_LOCK_DATE DATE NULL
/
the not null constraint was placed with your table definition (as is referred to by NULLABLE NO in your table display )
CREATE TABLE ..
(..
R_LOCK_DATE DATE NOT NULL
..
)
ALTER TABLE DM_SYSOBJECT_S MODIFY R_LOCK_DATE DATE NULL
/
the not null constraint was placed with your table definition (as is referred to by NULLABLE NO in your table display )
CREATE TABLE ..
(..
R_LOCK_DATE DATE NOT NULL
..
)
>>or just alter the column definition (= removing the contraint)
I would not suggest doing this 'just because'. A constraint in placed on a table for a reason. If you remove it you can cause serious application issues.
I would not suggest doing this 'just because'. A constraint in placed on a table for a reason. If you remove it you can cause serious application issues.
select constraint_name
from user_cons_columns
where table_name='DM_SYSOBJECT_S
Once you get the name of the constraint, you can query USER_CONSTRAINTS for the CONSTRAINT_TYPE.