Re: sqlldr problem a tough one???

From: Ranga Chakravarthi <ranga_at_nospam.cfl.rr.com>
Date: Fri, 03 Aug 2001 14:54:35 GMT
Message-ID: <LWya7.74129$Gh1.12248298@typhoon.tampabay.rr.com>

Peter,

A similar question was asked before.
In your control file, just treat the two fields as one by using the date format like this :
sdate date 'DDMONYYYY" "HH24:MI:SS'
it will be okay as long as you have a fixed number of spaces between the end quote of the date field and the begin quote of the time field in your input file.

HTH,
Ranga Chakravarthi

"Peter Shankey" <shankeyp_at_its.charlestoncounty.org> wrote in message
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 Received on Fri Aug 03 2001 - 09:54:35 CDT