I have a table created as
create table test
(test_default number(4));
Now, I have to add default value 8888 to the column if the length of inserted value is greater than 4.
I have tried
alter table test modify test_default DEFAULT 8888 when length(test_default)>4 ;
but this does not work. I'm not sure if this can be done.
Well, with the column size set at 4 you will get an ORA-01438: value larger than specified precision allows for this column error when attempting to enter a number with a length greater than 4 digits. Provided you allow a larger value to be entered, a trigger is really what you want (first change the column to a NUMBER(5)):
create or replace trigger test_BIU
before insert or update
on test
for each row
begin
if length(:new.test_default) > 4 then
:new.test_default := 8888;
end if;
end test_BIU;
Now insert some data:
SQL> insert into test values(12345);
1 row created.
SQL> select *
2 from test;
TEST_DEFAULT
------------
8888
SQL>
Use alter modify
alter table test modify( test_default number(4) default 888 );
