Re: Q:SQL*Loader Problem with Date ORA-01861

Hi Daniel,
Well, I changed my Controlfile:
LOAD DATA
INFILE 'R8'
into table R8
fields terminated by "," optionally enclosed by '"' (

	notesdocid,
	gpfearcid,
	arclevel,
	decdocid INTEGER EXTERNAL TERMINATED BY WHITESPACE,
	doctype INTEGER EXTERNAL TERMINATED BY WHITESPACE,
	ablagekreis,
	mandant,
	rechnr,
	dokdatum DATE "YYYY-MM-DD",
	kundennr

...BUT, the same Error occurs! May the quotes be a problem?

>>>>>>>>>>>>>>>>>> Ursprüngliche Nachricht <<<<<<<<<<<<<<<<<<

Am 30.10.2002, 17:14:28, schrieb Daniel Morgan <dmorgan_at_exesolutions.com> zum Thema Re: Q:SQL*Loader Problem with Date ORA-01861:

> Ralf Bender wrote:

> > Hi,
> >
> > i want to fill in my Table R8 with CSV Data. The file looks like this:
> > "38F ","38F
> > ","A",911 ,5001 ,"01","001","6183090","2000-03-10","0010610 "
> > "390 ","390
> > ","A",912 ,5001 ,"01","001","6183100","2000-03-10","0010610 "
> > "391 ","391
> > ","A",913 ,5001 ,"01","001","6183360","2000-03-10","0010620 "
> >
> > My Problem is the date. I always get a ORA-01861 Error. Here is my
> > Controlfile:
> > LOAD DATA
> > INFILE 'R8'
> > into table R8
> > fields terminated by "," optionally enclosed by '"'
> > (
> > notesdocid,
> > gpfearcid,
> > arclevel,
> > decdocid INTEGER EXTERNAL TERMINATED BY WHITESPACE,
> > doctype INTEGER EXTERNAL TERMINATED BY WHITESPACE,
> > ablagekreis,
> > mandant,
> > rechnr,
> > dokdatum "to_date(:DOKDATUM,'YYYY-MM-DD')",
> > kundennr
> > )
> >
> > What's wrong with dokdatum?
> > Can Someone help me?
> >
> > Thx
> > Ralf

> Oracle doesn't see dates ... it see's strings.

> You either need to convert 2000-03-10 to ... I don't know ...is it
> YYYY-DD-MM or YYYY-MM-DD. Even I can't figure it out so Oracle certainly
> can't.

> So you need to indicate the date formatting as in the following example:
> LOAD DATA
> INFILE *
> APPEND
> INTO TABLE emp
> FIELDS TERMINATED BY ","
> OPTIONALLY ENCLOSED BY '"'
> (empno, ename, job, mgr, hiredate DATE "DD-Month-YYYY",
> sal, comm, deptno CHAR TERMINATED BY ':',
> projno, loadseq SEQUENCE(MAX,1))
> BEGINDATA
> 9782,"Clark",Manager",7839, 09-June-2000, 2572.50,, 10:101
> 9839,"King","President", , 17-November-1999, 5500.00,,10:102
> 9934,"Miller","Clerk",7782, 23-January-2001, 920.00,, 10:102
> 9566,"Jones","Manager",7839, 02-April-2001, 3123.75,, 20:101
> 9499,"Allen","Salesman",7698, 20-February-2001, 1600.00, 300.00, 30:103
> 9654,"Martin","Salesman",7698, 28-September-2000, 1312.50, 1400.00,
30:103

> 9658, "Chan", "Analyst", 7566, 03-May-1999, 3450,, 20:101

> Daniel Morgan
> http://www.extension.washington.edu/extinfo/certprog/oad/oad_crs.asp
Received on Wed Oct 30 2002 - 10:39:34 CST