Re: ORA-01830 On Delete but not on Select?

On Mar 23, 8:42 am, "Jens Mohrmann" <jmohrm..._at_snafu.de> wrote:
> Anurag,
>
> unfortunateley there are no triggers defined for this table.
>
> Even a
>
> create table testlog as select * from mflogbook;
> commit;
> DELETE FROM testlog WHERE TO_DATE(TRIM(DATETIME), 'DD.MM.YYYY
> HH24:MI:SS')+30 < TO_DATE('22.03.2007', 'DD.MM.YYYY');
>
> was yielding the same ORA-01830.
>
> Do you have another idea?
>
> Thanks
>
> Jens Mohrmann
>
> "Anurag Varma" <avora..._at_gmail.com> schrieb im Newsbeitragnews:1174599402.805030.203220_at_o5g2000hsb.googlegroups.com...
>
>
>
>
>
> > Are there any triggers on that table which might be causing this?
> > Anurag- Hide quoted text -
>
> - Show quoted text -

My suggestion is you have 'junk' in your table (obviously you store dates as varchar2 [which you shouldn't have done] and you don't enforce the exact format), which you hit during delete as the execution path for the delete is a full table scan, and during the select isn't.

--
Sybrand Bakker
Senior Oracle DBA