RE: Sqlldr constants and dates w/timestamp

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Thu, 28 Oct 2004 12:26:04 -0400
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE07A7F9E9@exchsen0a1ma>

Darlene,

Did you try:

deploydatetime CONSTANT "to_date('04-OCT-2004 14:00:00','dd-MON-yyyy hh24:mi:ss')",

You can also try this without the "CONSTANT" word. Should work fine.

Good Luck.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

From: Marley, Darlene [mailto:dmarley_at_sfwmd.gov] Sent: Thursday, October 28, 2004 12:15 PM To: oracle-l_at_freelists.org
Subject: Sqlldr constants and dates w/timestamp

Maybe someone can save my bloody head from beating it against the wall.

I'm using sqlldr 8.1.7 and I'm trying to load a file that needs supplemental constant values:

OPTIONS(SKIP=19) LOAD DATA INFILE 'WCA1MESO.csv'

APPEND INTO TABLE erid.loaddiel

FIELDS TERMINATED BY "," Optionally enclosed by '"' TRAILING NULLCOLS

(

sonde CONSTANT "33214",

deploydepth CONSTANT ".64",

retrievaldepth CONSTANT ".63",

deploydatetime CONSTANT "04-OCT-2004 14:00:00", -- date format DD-MON-YYYY HH24MISS blah, blah,blah

I've set my environment variable NLS_DATE_FORMAT to 'DD-MON-YYYY HH24MISS', but it still pukes on deploydatetime with the error "ORA-01830: date format picture ends before converting entire input string"

Any ideas?

TIA Bloody in FL.

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 28 2004 - 11:23:52 CDT