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

Ralf Bender wrote:

> 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

The problem appears to be an inconsistency with the NLS Date format in your database.

Daniel Morgan Received on Wed Oct 30 2002 - 14:30:31 CST