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 ?

Can you post the actual DDL of your table? The error you are getting strongly implies that EventDate is declared as a VARCHAR2, not as a TIMESTAMP. — Justin Cave, Feb 02 '11 at 22:06
Or that the problem is not in the trigger but in the actual INSERT statement. — Justin Cave, Feb 02 '11 at 22:21

score 2 · Answer 1 · answered Feb 02 '11 at 21:40

2

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;

answered Feb 02 '11 at 21:40

Justin Cave

227,342
24
367
384

score 1 · Answer 2 · answered Feb 02 '11 at 23:17

1

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;

answered Feb 02 '11 at 23:17

Gary Myers

34,963
3
49
74

Thanx. I actually on my way to learn Oracle built in functions. – Nickolodeon Feb 03 '11 at 00:54

score 0 · Answer 3 · answered Feb 02 '11 at 21:41

0

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;

answered Feb 02 '11 at 21:41

Craig

5,740
21
30

Because it yields the same error, that actual width 11 is too large for column with width 7! – Nickolodeon Feb 02 '11 at 21:50

score 0 · Answer 4 · answered Feb 02 '11 at 21:44

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;
/

I'm using 10g. I can't seem to find a way modify Width property of that column. It is 7 and that is all to it( I need 11, according to exception — Nickolodeon, Feb 02 '11 at 21:47
I don't believe you don't neet 10g. Rather, you need to reduce the length of another value you try to insert. — René Nyffenegger, Feb 02 '11 at 21:49

score 0 · Answer 5 · answered Feb 03 '11 at 00:56

0

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.

answered Feb 03 '11 at 00:56

Nickolodeon

2,848
3
23
38

Oracle SYSDATE question for datetime column ORA 12899

5 Answers5