Oracle-L: RE: SQL*Loader

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Mon, 08 Apr 2002 09:56:36 -0800
Message-ID: <F001.0043E99E.20020408095636@fatcity.com>

Dave,

I bet you the single quotes around the data string are messing you up. You could try the following:

DATETESTED char "rtrim(ltrim(to_date(:DateTested,'mm/dd/yyyy hh:mi:ss pm'),''''),'''')",

Hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Monday, April 08, 2002 12:16 PM
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: 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
--------------------------------------------------------------------