Re: Constant Date in SQL*Loader Control File

David,

        Have you tried removing the " around the TO_DATE call? The " indicate a character string and Oracle is unable to translate that string into something acceptable for a DATE datatype.

David Lewandowski wrote:
>
> Thanks Lisa. But regrettably my LOADDATE isn't always SYSDATE. Do you know
>
> the syntax for an arbitrary date?
>
> Dave
>
> -----Original Message-----
> Sent: Thursday, June 26, 2003 1:09 PM
> To: Multiple recipients of list ORACLE-L
>
> Hi David,
>
> Here's one of my control files, it works for me... LOAD_DATE is indeed a
> date field.
>
> OPTIONS (DIRECT=TRUE, PARALLEL=FALSE, BINDSIZE=1048576)
> UNRECOVERABLE
> LOAD DATA
> INFILE 'D:\FTPRoot\vegas\ascname.txt'
> BADFILE 'D:\data\vegas\log\ascname.bad'
> DISCARDFILE 'D:\data\vegas\log\ascname.dsc'
> TRUNCATE
> INTO TABLE ASCNAME_RAW
> (
> ID RECNUM,
> AN_SITE POSITION(1:2) CHAR
> NULLIF AN_SITE=BLANKS,
> AN_ASSOC_NO POSITION(3:6) CHAR
> NULLIF AN_ASSOC_NO=BLANKS,
> AN_ASSOC_NAME POSITION(7:36) CHAR
> NULLIF AN_ASSOC_NAME=BLANKS,
> AN_ASSOC_LEGAL_NAME POSITION(37:80) CHAR
> NULLIF AN_ASSOC_LEGAL_NAME=BLANKS,
>
> LOAD_DATE SYSDATE
> )
>
> Lisa Koivu
> Oracle Database Monkey
> Fairfield Resorts, Inc.
> 5259 Coconut Creek Parkway
> Ft. Lauderdale, FL, USA 33063
> Office: 954-935-4117
> Fax: 954-935-3639
> Cell: 954-683-4459
>
> -----Original Message-----
> Sent: Thursday, June 26, 2003 12:40 PM
> To: Multiple recipients of list ORACLE-L
>
> I can't figure out how to specify a constant date for a column
> in a SQL*Loader control file and can't find any examples.
>
> I tried:
> LOADDATE CONSTANT "TO_DATE('20030626', 'YYYYMMDD')",
> but I get this error message in the log file:
> ORA-01858: a non-numeric character was found where a numeric was expected
>
> Can anyone tell me the correct syntax?
>
> David Lewandowski
> Focused Health Solutions
> [EMAIL PROTECTED]
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: David Lewandowski
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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.net
> --
> Author: Koivu, Lisa
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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.net
> --
> Author: David Lewandowski
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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).begin:vcard
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;;;;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard Received on Thu Jun 26 2003 - 14:30:13 CDT