Oracle SYSDATE question for datetime column ORA 12899

Question

I wrote a trigger in which I have a line:

SELECT * 
  INTO :NEW.EVENTDATE 
  FROM (SELECT SYSDATE 
          FROM DUAL);

For some reason this does not work (EVENTDATE column has timestamp(0) type).

When I try to insert something I get error message saying that value is too long for that column. I though SYSDATE and timestamp(0) would coalesce and understand each other.

What the ?

Answer 1

You should just do this in PL/SQL

:new.EventDate := SYSTIMESTAMP;

but if you want to use SQL

SELECT systimestamp
  INTO :new.EventDate
  FROM dual;

Answer 2

I don't know why this wouldn't work. Can you post the actual error (with code and everything) that you are getting?

I also don't know why you wouldn't just assign the variable:

:NEW.EVENTDATE := systimestamp;

Answer 3

What version are you running?

The following works fine on Oracle 11R2:

drop   table tq84_eventdate;

create table tq84_eventdate (
  data      varchar2(10),
  eventdate timestamp(0)
);

create trigger tq84_eventdate_trg 
before insert on tq84_eventdate 
for each row
begin

  SELECT * INTO :NEW.EVENTDATE FROM (SELECT SYSDATE FROM DUAL);

end tq84_eventdate_trg;
/


insert into tq84_eventdate (data) values ('test');

select * from tq84_eventdate;

However, if I do a

insert into tq84_eventdate (data) values ('value too large!');

I get the ORA-12899 you mentioned. So, the error is probably not related to the select statement you posted, but to the data that you actually try to insert.

Also, on a related note, you can assign sysdate directly in the trigger, that is without the indirection of a select statement:

create trigger tq84_eventdate_trg 
before insert on tq84_eventdate 
for each row
begin

  :new.eventdate := sysdate;

end tq84_eventdate_trg;
/

Answer 4

Try an explicit cast such as

select cast(sysdate as timestamp(0)) from dual

As a thought, is there anything exotic with your session's calendar settings which might force a unusual conversion. If so, try specifying the appropriate calendar in a conversion.

select to_char(sysdate,'DD-fmMonth-YYYY','nls_calendar=''Arabic Hijrah''') from dual;

Answer 5

I was able to exhort my bosses to convert one column's type to another. Namely timestamp to DATE.

Strangely enough nobody throughout this branch has not pointed out that I could substitute Timestamp field with DATE field.