Re: Loading invalid dates using SQL Loader

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 22 Dec 1998 19:34:53 GMT
Message-ID: <367ff2dc.1861767@192.86.155.100>

A copy of this was sent to imrad_at_icubed.com (if that email address didn't require changing) On Tue, 22 Dec 1998 18:05:06 GMT, you wrote:

>I was wondering if anyone knows a way to detect an invalid date when using
>SQLLoader.
>
>I'm loading data in from a flat file. The date comes to me in the following
>format 19980228. Since the source system that generates this data doesn't
>check for a valid date I run into invalid dates when loading (19980230,
>19981575, etc..) The majority (98%) are dates with incorrect last days of the
>month.
>
>Currently the rows that have invalid dates are being loaded into a bad file.
>I then edit the bad file and update the date then I load the bad rows. This
>takes time and is a manual process.
>
>I know of the nullif and defaultif functions but I don't think they can help
>me.
>
>Here is what I would like to accomplish using SQLloader.
>

if you are using the conventional path loader, you can do this with pl/sql pretty easily. Consider:

create or replace function my_to_date( p_str in varchar2 ) return date is
begin

    begin

        return to_date( p_str, 'YYYYMMDD' );     exception

        when others then
        begin
            return last_day( to_date( substr(p_str,1,6), 'YYYYMM' ) );
        exception
            when others then
                return SYSDATE;
        end;

    end;
end;
/

which is logic sort of like you asked for (not 100%, i return sysdate instead of the first day of the year cause I didn't know what year you meant for a date like 19??0225)....

Then, you could use a control file that looks like:

LOAD DATA
INFILE *
INTO TABLE TEST
replace
(X position(1:8) "my_to_date(:x)",  y position(1:8) )
BEGINDATA
19980228
19980230
19??0131
20??0131

and the data loaded would be:

SQL> desc test

 Name                            Null?    Type
 ------------------------------- -------- ----
 X                                        DATE
 Y                                        VARCHAR2(255)

SQL> select * from test;

X Y

--------- ----------
28-FEB-98 19980228
28-FEB-98 19980230
22-DEC-98 19??0131
22-DEC-98 20??0131

>If a date has a incorrect last day of the month (19980230) make the date the
>last day of that month and year.
>(19980228)
>
>If a date has a incorrect month or year make the date the first day of that
>year.
>(19??0131) or (20??0131)
>
>In reviewing the Oracle documentation on SQLloader this seems impossible BUT I
>thought I would ask the Oracle Experts before writing PL/SQL code to check for
>valid dates before I use SQL loader.
>
>Russ
>imrad_at_icubed.com
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Dec 22 1998 - 13:34:53 CST