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:14:28 CST