| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL*Loader
Dave,
I just ran the following and it worked just fine:
Sql_Loader file:
LOAD DATA
INFILE 'test_loader1.dat'
replace
INTO TABLE test
(
DATETIME POSITION(01:21) CHAR
"to_date(rtrim(ltrim(:datetime,''''),''''),'yyyy-mm-dd-hh24.mi.ss')",
USERID POSITION(23:30) CHAR)
Data File:
'1999-02-05-09.26.13' 416000ZHUP
'1999-02-05-09.26.13' 416000ZHUP
'1999-02-05-09.26.13' 416000ZHUP
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Monday, April 08, 2002 1:57 PM
To: Multiple recipients of list ORACLE-L
Here is an example of my logfile that it is generated:
SQL*Loader: Release 8.1.6.0.0 - Production on Mon Apr 8 10:06:31 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Control File: app_dev.testcaseuatstatus.ctl Data File: app_dev.testcaseuatstatus.dat Bad File: app_dev.testcaseuatstatus.badDiscard File: none specified
(Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65536 bytes Continuation: none specified Path used: Conventional
Table APP_DEV.TESTCASEUATSTATUS, loaded from every logical record. Insert option in effect for this table: INSERT TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ------------------
---
TESTCASESTATUSID FIRST 4 INTEGER
TESTCASEID NEXT 4 INTEGER
USERID NEXT 4 INTEGER
CORDID NEXT 4 INTEGER
UATASSIGNED NEXT 4 INTEGER
PASSFAILSTATUSID NEXT 4 INTEGER
DATETESTED NEXT * , " CHARACTER
SQL string for column : "to_date(:DateTested,'mm/dd/yyyy hh:mi:ss pm')"
TASKID NEXT 4 INTEGER
RETEST NEXT 4 INTEGER
ASSID NEXT 4 INTEGER
NONVALID NEXT 4 INTEGER
Record 1: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column
DATETESTED.
Initial enclosure character not found
Record 2: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column
DATETESTED.
Initial enclosure character not found
Record 3: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column
DATETESTED.
Initial enclosure character not found
Record 4: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column
DATETESTED.
Initial enclosure character not found
Record 5: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column
DATETESTED.
Initial enclosure character not found
Table APP_DEV.TESTCASEUATSTATUS:
0 Rows successfully loaded.
5 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 19072 bytes(64 rows) Space allocated for memory besides bind array: 0 bytes Total logical records skipped: 0 Total logical records read: 5 Total logical records rejected: 5 Total logical records discarded: 0
Run began on Mon Apr 08 10:06:31 2002
Run ended on Mon Apr 08 10:06:36 2002
Elapsed time was: 00:00:05.96 CPU time was: 00:00:03.92
thanks alot,
David Ehresmann.
> -----Original Message----- > From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of John Hallas > Sent: Monday, April 08, 2002 12:10 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: SQL*Loader > > > David, > What does your log file show as the reason for failure? > John > -----Original Message----- > Sent: 08 April 2002 17:16 > To: Multiple recipients of list ORACLE-L > > I am trying to user SQL*Loader to load some tables in my 8i database. The > data will not load. It seems to have to do with the format. In > particular > the date format. Can anybody help? I have messed with this for 2 days. > > Here is my data file (only 5 rows displayed): > > 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 > 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 > 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 > 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 > 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 > > Here is my control file: > > LOAD DATA > INSERT > INTO TABLE APP_DEV.TESTCASEUATSTATUS > FIELDS TERMINATED BY "," ENCLOSED BY '"' > TRAILING NULLCOLS > (TESTCASESTATUSID INTEGER, > TESTCASEID INTEGER, > USERID INTEGER, > CORDID INTEGER, > UATASSIGNED INTEGER, > PASSFAILSTATUSID INTEGER, > DATETESTED char "to_date(:DateTested,'mm/dd/yyyy hh:mi:ss pm')", > TASKID INTEGER, > RETEST INTEGER, > ASSID INTEGER, > NONVALID INTEGER) > > I have also tried: DATETESTED date 'mm/dd/yyyy hh:mi:ss pm' this string > for the date field. > > > David Ehresmann > Oracle DBA 8i OCP > MCI Worldcom > c-david.ehresmann_at_wcom.com > 972.656.1015 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: David Ehresmann > INET: c-David.Ehresmann_at_wcom.com > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: John Hallas > INET: john.hallas_at_hcresources.co.uk > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). >
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann INET: c-David.Ehresmann_at_wcom.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Apr 08 2002 - 13:20:36 CDT
 |
 |