Re: Help! I need a bad records tablev

From: L120bj <l120bj_at_aol.com>
Date: 1998/01/25
Message-ID: <19980125135001.IAA01017@ladder01.news.aol.com>#1/1

>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 Sun Jan 25 1998 - 00:00:00 CST