SQL Loader error!

[Unna]

Hi all,

I am trying to insert data in comma delimited file into Oracle database sitting in Unix server. After I ran the script, I got error message as,

Ora8_Acct: sqlload scott/tigger /export/home/dbase/cat.ctl

SQL*Loader: Release 8.0.4.0.0 - Production on Wed Apr 25 13:30:44 2001

(c) Copyright 1997 Oracle Corporation. All rights reserved.

SQL*Loader-930: Error parsing insert statement for column CATEG.CDATE.
ORA-00984: column not allowed here


Control file - cat.ctl

load data
infile '/export/home/dbase/cat.dat'
append
into table categ
fields terminated by "," optionally enclosed by '"'
(cat_id,lis_id,catnme,desct,ptid,stname,mdate "MM/DD/YY",
ordpt,cdate "MM/DD/YY")


Data File - cat.dat

"1","1","Antennas/Base Station Equipment","","","ACTIVE","2/2/01","1","1/18/01"
"2","1","Cable","","","ACTIVE","1/24/01","1","1/18/01"
"3","1","Call and Voice Equipment and Systems","","","ACTIVE","1/24/01","1","1/18/01"



I looked up the error codes, and check the data structure, still couldn't resolve it. Can anyone give me suggestions?

Thanks

[jdorlon]

Try putting the keyword DATE between the name of the column and the format... I am not sure that this is the
problem, but that is how I always do it....

(cat_id,lis_id,catnme,desct,ptid,stname,mdate DATE "MM/DD/YY", ordpt,cdate DATE "MM/DD/YY")

hth,

-John

[pss]

Okey. You have "MM/DD/YY" date format mask and values like "1/24/01". You do not have to enclose number values into "

Sergey.

[Unna]

John,

Thanks! After adding DATE and ran the script again, I got this error,

SQL*Loader-522: lfiopn failed for file (/export/home/dbase/cat.bad)

Where does it go wrong?

Thanks,
Unna

[Unna]

I removed the ", but still getting these SQL*Loader-930 and ORA-00984 error messages

[jdorlon]

Unna,

It sounds like now you are experiencing a problem with permission to write into the /export/home/dbase/ directory.
SQL Loader wants to write records that it could not import to this .bad file.

-John

[Unna]

Thank you for all your help. I got it works now, and it's indeed this problem.

Thx,
Unna