Re: sqlldr problem a tough one???

From: drew <drewb_at_dnai.com>
Date: Thu, 23 Aug 2001 05:16:23 GMT
Message-ID: <9m23q502fod@enews1.newsguy.com>

Pete,

My server is down for backups right now, so I can't test my idea; but how about if you tried something like:

LOAD DATA
INFILE 'logfile.dat'
DISCARDFILE 'logfile.dsc'
APPEND
INTO TABLE FWLOG
(
SDATE POSITION(1:25) CHAR

                   "TO_DATE(TRANSLATE(:SDATE,'"',' '),' DDMONYYYY    HH24:MI:SS
        '),

SERVICE TERMINATED BY WHITESPACE ENCLOSED BY '"', SOURCE TERMINATED BY WHITESPACE ENCLOSED BY '"', DESTINATION TERMINATED BY WHITESPACE ENCLOSED BY '"', USERID TERMINATED BY WHITESPACE ENCLOSED BY '"', INFO ENCLOSED BY '"'
)

Since you can't use FILLER in SQL expressions, it's this approach or a temporary table or preprocessing the data prior to loading.

hope this helps,

drew

On 2-Aug-2001, Peter Shankey <shankeyp_at_its.charlestoncounty.org> wrote:

> I am having a sql loader problem (oracle 8.1.7) which I can not seem to
> figure out.
>
> the table looks like this:
>
>
>
> SQL> desc fwlog
> Name Null? Type
> -------------------------------
> SDATE NOT NULL DATE
> SERVICE varchar2(50)
>
> SOURCE VARCHAR2(15)
> DESTINATION VARCHAR2(100)
> USERID VARCHAR2(100)
> INFO CLOB
>
> SQL>
>
> The data is generally Variable-Length data this sample data may-be line
> wrapping for you but each row is one line.
>
> "16Jul2001" " 8:07:48" "http" "208.161.140.43" "outpostrr1.real.com"
> "" " resource http://207.188.7.131:80/getlatest.glh"
> "16Jul2001" " 8:07:48" "http" "208.161.140.43" "207.188.7.36" "" "
> resource
> http://207.188.7.36:80/issues/01/07/14/0/010706_bigbro_krista.gd"
> "16Jul2001" "11:52:38" "http" "208.161.140.43" "www9.dcx.yahoo.com"
> "" " resource http://64.58.76.178:80/"
> "16Jul2001" "13:50:22" "http" "208.161.140.43" "www2.yahoo.com" ""
> " resource http://204.71.200.67:80/"
>
>
> my control file is
>
> LOAD DATA
> INFILE 'logfile.dat'
> DISCARDFILE 'logfile.dsc'
> APPEND
> INTO TABLE FWLOG
> FIELDS TERMINATED BY " " ENCLOSED BY '"'
> (
> SDATE DATE "DDMONYYYY'"' '"'HH24:MI:SS",
> SERVICE,
> SOURCE,
> DESTINATION,
> USERID,
> INFO
> )
>
> Now for my problem
> The problem I am having is in the dat file the date and time are broken
> up into two parts. I have tried twiddling with the control file in order
> to get the first to dat file columns into the single date field however I
> have not had any success. I have tried
> SDATE DATE "DDMONYYYY\" \"'HH24:MI:SS",
> and many variation. If anyone has any ideas I would be most thankful
>
> thanks
> Pete
Received on Thu Aug 23 2001 - 00:16:23 CDT