Re: Help! I need a bad records tablev

Just a little warning. The loop with exception of the type OTHERS handling inside can easily become infinite. Therefore a better solution would be to declare a set of exceptions, corresponing to Oracle error codes for date conversion (ORA-01840, ORA-01843, etc.), and catch only them. Regards.
Alexei Fox

L120bj wrote:

> >Subject: Help! I need a bad records tablev
> >From: Alvaro Illarze <illarze_at_chasque.apc.org>
> >Date: 1/20/98 10:12PM GMT
> >Message-id: <885333279.967114769_at_dejanews.com>
> >
> >Hi!
> > I'm writing this because I'm having quite a big problem.
> > I'm writing a sql*plus script for an Oracle 7 Server, to
> >automate a conversion from a char column to a date column. The thing
> >is that there is at least one record which (I don't know why) is not
> >a valid date. When Oracle finds this record, it display a warning and
> >stop. How can I tell Oracle to go on working (and if possible send this
> >record to a bad file table)??
> > I know that SqlLoad do something like this (keeps on working and
> >throw the record to a bad file), ANYBODY out there knows how can I
> >make it work? I'll be very grateful.
> >
> > Alvaro Illarze
> >
> >-------------------==== Posted via Deja News ====-----------------------
> > http://www.dejanews.com/ Search, Read, Post to Usenet
> >
> >
> >
> >
> >
> >
> >
> You could use the following PL/SQL code as a basis for such a routine
>
> set serverout on size 1000000
> declare
>
> cursor c_conv_date is
> select type, rowid
> from t1;
>
> r_conv_date c_conv_date%rowtype;
> l_Date date;
> begin
> open c_conv_date;
> loop
> begin
> fetch c_conv_date into r_conv_date;
> if c_conv_Date%notfound then
> exit;
> end if;
> l_Date := to_Date(to_char(r_conv_date.type),'RRMMDD');
> dbms_output.put_line('Successful conversion of
> '||to_char(r_conv_date.type));
> exception
> when others then
> dbms_output.put_line(SQLERRM);
> dbms_output.put_line(rowidtochar(r_conv_date.rowid)||'-'||to_char(
> r_conv_date.type));
> end;
> end loop;
> close c_conv_Date;
> end;
> /
>
> Hope this helps
> Rob
Received on Thu Jan 29 1998 - 00:00:00 CST