RE: SQL*Loader Date error (ORA-01858)

From: Jim Silverman <jsilverman_at_Solucient.com>
Date: Wed, 7 Dec 2005 15:32:04 -0500
Message-ID: <2D1EFB433A527D4681B80C94B71E359803BBF57D@aaexchange01.HEALTH.AD>

This may be a silly question, but are you sure that the column names in the controlfile correspond properly to the columns in your table RE_STATS_STAGING? In particular, are you sure that the datatypes of the input data (including the timestamp constant) are compatible with the datatypes in the table definition?

---

Jim Silverman
Senior Systems Database Administrator
Solucient, LLC
Telephone: 734-669-7641

FAX:            734-930-7611 
E-Mail:         jsilverman_at_solucient.com 

		From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Madhavi Kanugo
		Sent: Wednesday, December 07, 2005 1:20 PM
		To: 'oracle-l_at_freelists.org'
		Subject: SQL*Loader Date error (ORA-01858)

		Hello  All,

		I'm trying to load data using SQL Loader and ran into

the ORA-01858: a non-numeric character was found where a numeric was expected error.

                Below is my control file. As you can see, the timestamp is a constant field and I am loading its value from the filename of the data files. I am executing the sql loader from a shell script and creating the control file on the fly. The filename is in the form of: 20051206130101.txt So basically, the shell script replaces the when_changed value with 20051206130101.

                I created a temp table with just a date field and tried to insert a value into it. Insert into temp values (to_date('20051207160752','YYYYMMDDHH24MISS')); and there is no error and date conversion is implict.

                But SQLLOADER is erring out on ORA-01858.

		LOAD DATA 
		INFILE FILENAME 
		APPEND 
		INTO TABLE RE_STATS_STAGING
		FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
		    TRAILING NULLCOLS 
		( 
		  RE_NAME,
		  TG_TYPE,
		  TRUNKGROUP,
		  CONNECTS,
		  HANGUPS,
		  DECLINES,
		            LOOPS,
		            SECONDS,
		            CC_CONNECTED,
		            CC_SETUP,
		            TIMESTAMP CONSTANT
"to_date('_when_changed_','YYYYMMDDHH24MISS')"
		        )

		Any ideas on where I am going wrong? Any thoughts or

help will be appreciated.

		Thanks in advance,
		Madhavi

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

Received on Wed Dec 07 2005 - 14:32:24 CST