• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3632
  • Last Modified:

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!
0
Kittie
Asked:
Kittie
2 Solutions
 
ypwitkowCommented:
Change your control 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 DATE(15) 'DD-MON-YY HH24:MI',
 treatment CHAR,
 pet_id CHAR)

Your second question - yes, you have already record with this key in the table.
Greetings,
Lucy




0
 
KittieAuthor Commented:
Hi lucy, thanks for the 1st qn ans

However abt the 2nd qn:
If i use SEQUENCE(10,1) in the .ctl file...then i can't use seq.NEXTVAL anymore during INSERT INTO command??

cause after loading the data into table, I will also need to write some INSERT INTO statements. However when I use seq.NEXTVAL, there will be error...How can this be solve?
0
 
SDuttaCommented:
For your second question, when you create the sequence using the following syntax :

CREATE SEQUENCE seq_name START WITH start_value
INCREMENT BY inc_value MINVALUE min MAXVALUE max CYCLE|NO CYCLE CACHE some_val;

use a start_value for SEQ that is more than :
select max(appt_id) from AT_Appointments;
0
 
oraelbisCommented:
You can try execute bat file after loading data like:

sqlplus.exe username/passw@host P:\RebuildSeq.SQL

for executing sql script RebuildSeq.SQL like:

alter sequence SEQ increment by 1 nocache;
select SEQ.nextval from dual;
alter sequence SEQ increment by 1 nocache;
declare
  lastValue integer;
  max_no    number;
begin
  select max(Id_field) into max_no from AT_Appointments;
  loop
    select SEQ.currval into lastValue from dual;
    exit when lastValue >= max_no;
    select SEQ.nextval into LastValue from dual;
  end loop;
end;
/
alter sequence REPL_NO_SEQ increment by 1 cache 20;
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now