Re: difficulty with sqlldr

Ed_Stevens_at_nospam.noway.nohow (Ed Stevens) wrote in message news:<3b460a32.17426868_at_ausnews.austin.ibm.com>...
> Server is Ora 8.0.5 on NT 4, sp6. Client is 8.1.7.0.0 on Win2K.
>
> Using SQL*Loader to load a comma-delimited file to a test table.
>
> Table looks like this:
>
> SQL> desc nmm.eds_test
> Name Null? Type
> ------------------------------- -------- ----
> EMPNO NUMBER(38)
> DEPT VARCHAR2(8)
> CRTE_DATE DATE
> CRTE_TIME DATE
> CRT_FLAG VARCHAR2(5)
>
> Data file looks like this:
>
> 0,"04-B",20010619,"08.36.53","OFF"
> 1495,"04-B",20010619,"08.37.12","ON "
> 2497,"12-2",20010619,"08.43.52","ON "
>
> sqlldr control file looks like this:
>
> load data
> infile 'test_data.txt'
> discardfile 'c:\_temp\test_discard.txt'
> replace
> into table nmm.eds_test
> fields terminated by "," optionally enclosed by '"'
> (empno,
> dept,
> crte_date "yyyymmdd",
> crte_time "hh24-mi-ss",
> crt_flag)
>
>
> Error log shows this:
>
> Record 1: Rejected - Error on table NMM.EDS_TEST, column CRTE_TIME.
> ORA-00984: column not allowed
>
> ???????????

Hi Ed,

try this:

load data
infile 'test_data.txt'
discardfile 'c:\_temp\test_discard.txt'
replace
into table nmm.eds_test
fields terminated by "," optionally enclosed by '"'

	(empno,
	 dept,
	 crte_date "yyyymmdd",
 	 crte_time date(8) "hh24.mi.ss",
 	 crt_flag)

You have specified the wrong format modell in your Controlfile. Delimiter between hours and minutes in Controlfile is "-" but in Datafile you specifie it like this "08.36.53". I presume that this could be the error.

regard
thomas Received on Wed Jul 18 2001 - 04:38:02 CDT