loading date_time into table with sql loader

this is my appt.dat file:
04-MAR-03 9:30,Rabies vaccine,1000
04-MAR-03 9:45,Rabies vaccine,1001
04-MAR-03 10:00,Dental exam,1002

this is my appt.ctl file:
LOAD DATA
INFILE 'AT_Appointments.dat'
INTO TABLE AT_Appointments
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(appt_id SEQUENCE(10,1),date_time,treatment,pet_id)

this is the error in appt.log file:
Record 1: Rejected - Error on table AT_APPOINTMENTS, column DATE_TIME.
ORA-01830: date format picture ends before converting entire input string

Record 2: Rejected - Error on table AT_APPOINTMENTS, column DATE_TIME.
ORA-01830: date format picture ends before converting entire input string

Record 3: Rejected - Error on table AT_APPOINTMENTS, column DATE_TIME.
ORA-01830: date format picture ends before converting entire input string

I uses: SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI';
but it only works if i write the 'INSERT INTO' in the SQL> but not for the loader part.

How do I solve??

Q2) I uses squence for appt_id. But if after loading the .dat, I insert another record in the SQL>INSERT INTO AT_Appointments values (seq.NEXTVAL,....);

the error states violates the primary/unique key. why?Is it that seq.NEXTVAL starts at 10 and 10 had already been inserted during the loading?
So how shld I solve?

Please help anyone? ARGENT! Thanks!
Start Free Trial