I have a user who needs the possibility to enter just a year-number (ex. 2016) in a view containing a column that is defined as a date). The user see this value in the view as just a year (by using substr).

I have been trying to make a trigger which should convert the input to a date if it is inserted as a year, it seems to Work fine, but when I enter the date as 'DD-MM-YYYY' the trigger fails.

---- TABLE creation ----
CREATE TABLE table1
(
id NUMBER,
col1 DATE
);

insert into table1 values (1,'01-01-2014');
insert into table1 values (2,'25-08-2016');

---- VIEW creation ----
create view
view1
as
select
id,
SUBSTR (col1, 7) AS viewcol1
from
table1;

---- VIEW selection ----
select * from view1;


ID VIEWCOL1
---------- --------
1 2014
2 2016

2 rows selected.

---- VIEW update short attempt 1 ----
update view1
set
viewcol1 = '2015'
where
id=1;


ORA-01733: virtual column not allowed here

---- VIEW update long attempt 1 ----
update view1
set
viewcol1 = '01-01-2015'
where
id=1;
--same error

---- TRIGGER creation ----
create trigger trigger1
instead of update on view1
for each row
begin
update table1 set
col1 = :new.viewcol1
where
id = :old.id;
end;

---- VIEW update short attempt 2 ----
update view1
set
viewcol1 = '2015'
where
id=1;

ORA-01861: literal does not match format string
ORA-06512: at TRIGGER1, line 2
ORA-04088: error during execution of trigger 'TRIGGER1'

---- VIEW update long attempt 2 ----
update view1
set
viewcol1 = '01-01-2015'
where
id=1;
1 row updated.

---- TRIGGER recreation ----
create or replace trigger trigger1
instead of update on view1
for each row
declare
c_col1 varchar2(100);
begin
c_col1 := :new.viewcol1;

if length(:new.viewcol1) = 4 then
c_col1 := '01-01-'||:new.viewcol1;
else
c_col1 := :new.viewcol1;
end if;

update table1 set
col1 = c_col1
where
id = :old.id;
end;

---- VIEW update short attempt 3 ----
update view1
set
viewcol1 = '2012'
where
id=1;
1 row updated.


---- VIEW selection ----
select * from view1;

ID VIEWCOL1
---------- --------
1 2012
2 2016

2 rows selected.

---- TABLE selection ----
select * from table1;

ID COL1
---------- ----------
1 01-01-2012
2 25-08-2016

2 rows selected.


---- VIEW update long attempt 3 ----
update view1
set
viewcol1 = '01-07-2012'
where
id=1;

Error at line 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "TRIGGER1", line 4
ORA-04088: error during execution of trigger 'TRIGGER1'

Script Terminated on line 1.

update view1 set viewcol1 = '01-07-2012' where id=1;

SQL> desc view1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID                                     NUMBER
 VIEWCOL1                               VARCHAR2(4)

create or replace view view1 as
select id, cast(substr ( col1, 7 ) as varchar2(20)) as viewcol1 
from table1;

create or replace trigger trigger1 instead of
  update on view1 for each row 
declare c_col1 varchar2 ( 100 ) ;
begin
  c_col1                     := :new.viewcol1;
  if length ( :new.viewcol1 ) = 4 then
    c_col1                    := '01-01-'||:new.viewcol1;
  else
    c_col1 := :new.viewcol1;
  end if;
  update table1 t set t.col1 = c_col1 where t.id = :old.id;
end;
/

desc view1

update view1 set viewcol1 = '01-07-2012' where id=1;

select * from table1;

        ID COL1
---------- ----------
         1 01-07-2012
         2 25-08-2016