Re: sqlldr problem a tough one???

Can't you first manipulate the .dat file to join the date and time parts together?
This should be easily done by awk, Perl, or sed! So you may write a Master shell script that first call the awk script to modify the dat file into a new dat file, then call the sqlldr to read in the new dat file as you normally do, then delete the temp new dat file. Job DONE! Oracle is not the whole world.

--
Best Regards,
Kai Yuen Kiang

MIS Analyst
Cytech Technology Ltd.
Tel:  (852) 2378-2225
Fax: (852) 2375-7700
http://www.cytecht.com


"Peter Shankey" <shankeyp_at_its.charlestoncounty.org> ?????

news:20010802.19292000_at_its.charlestoncounty.org...
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