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

On Mar 23, 4:54 pm, "Jens Mohrmann" <jmohrm..._at_snafu.de> wrote:
> Sybrand and Gabriel,
>
> Thank you.
> That was the problem. Select was pausing after 500 records while the error
> occurred after rownum 2460.
>
> And I try to change the field to a DATETIME type instead of a VARCHAR2
> field.
>
> Jens Mohrmann
>
> "Gabriel" <spiderm..._at_hotmail.com> schrieb im Newsbeitragnews:eu0ce9$3pb$1_at_news.rd.francetelecom.fr...
>
> > Hello,
>
> > Do you fetch all the lines with the select ? The delete process all lines,
> > but if you have a set pause and set lines, only the first lines are fetch,
> > and the error may be elsewhere on the lines.
>
> > Regards.
>
> > Ps, sorry for my english, it's not my language.
>
> > "Jens Mohrmann" <jmohrm..._at_snafu.de> a écrit dans le message de news:
> > 56hemjF296ql..._at_mid.uni-berlin.de...
> >> 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 Newsbeitrag
> >>news:1174599402.805030.203220_at_o5g2000hsb.googlegroups.com...
>
> >>> Are there any triggers on that table which might be causing this?
> >>> Anurag

How about

SELECT * FROM mflogbook
WHERE TO_DATE(SUBSTR(LTRIM(datetime),1,10), 'DD.MM.YYYY') < DATE '2007-02-20';

Then at least you are not affected by junk after the 10th character, which you don't use anyway. Received on Sat Mar 24 2007 - 05:12:28 CDT