?
Solved

loading date_time into table with sql loader

Posted on 2003-03-31
6
Medium Priority
?
3,607 Views
Last Modified: 2007-12-19
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
Comment
Question by:Kittie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 2

Accepted Solution

by:
ypwitkow earned 100 total points
ID: 8237302
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
 

Author Comment

by:Kittie
ID: 8238732
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
 
LVL 10

Assisted Solution

by:SDutta
SDutta earned 100 total points
ID: 8240243
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
 
LVL 3

Expert Comment

by:oraelbis
ID: 8245757
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question