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='3d00c0ba81cbd5c6'

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.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

You can find it with:

select constraint_name
from user_cons_columns
where table_name='DM_SYSOBJECT_S' and column_name='R_LOCK_DATE'

Once you get the name of the constraint, you can query USER_CONSTRAINTS for the CONSTRAINT_TYPE.

flow01

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
..
)

slightwv (䄆 Netminder)

>>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.