Home » RDBMS Server » Server Utilities » SQLPlus loading data
SQLPlus loading data [message #298012] Mon, 04 February 2008 09:46 Go to next message
zepalways
Messages: 15
Registered: February 2008
Junior Member


I tried to load a csv file and then append them to temp_emp table.

In the file:
Jones,Mr,Peter,1 The Hirst,,
Filsham,Mr,William,104 Elli Road,(01212) 23232,15-Feb-98
Welli,Mr,George,High Lala,(0122) 1212121,

CREATE table temp_emp
(
last_name varchar2(20) ,
title varchar2(15),
first_name varchar2(20),
add1 varchar2(35),
home_tel varchar2(20),
date_started date,
pcode varchar2(10)
) ;


--MY CONTROL DATA file
LOAD DATA
APPEND INTO TABLE temp_emp
(
last_name CHAR TERMINATED BY ',',
title CHAR TERMINATED BY ',',
first_name CHAR TERMINATED BY ',',
add1 CHAR TERMINATED BY ',',
home_tel CHAR TERMINATED BY ',',
date_started DATE "DD-MON-YY" NULLIF date_started=BLANKS,
pcode FILLER DECIMAL EXTERNAL TERMINATED BY ','
)


I checked from the log file and I got these errors:

Rejected - Error on table TEMP_EMP, column PCODE.
Column not found before end of logical record (use TRAILING NULLCOLS)

Rejected - Error on table TEMP_EMP, column DATE_STARTED.
Column not found before end of logical record (use TRAILING NULLCOLS)

As a result, data is not appended.

anyone knows what should I do?
Re: SQLPlus loading data [message #298015 is a reply to message #298012] Mon, 04 February 2008 10:08 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Like the error message says, use TRAILING NULLCOLS:


LOAD DATA
APPEND INTO TABLE temp_emp
TRAILING NULLCOLS
(
last_name CHAR TERMINATED BY ',',
title CHAR TERMINATED BY ',',
first_name CHAR TERMINATED BY ',',
add1 CHAR TERMINATED BY ',',
home_tel CHAR TERMINATED BY ',',
date_started DATE "DD-MON-YY" NULLIF date_started=BLANKS,
pcode FILLER DECIMAL EXTERNAL TERMINATED BY ','
)
Previous Topic: Upgrade and Migrate
Next Topic: Import export
Goto Forum:
  


Current Time: Thu Dec 08 18:09:33 CST 2016

Total time taken to generate the page: 0.07442 seconds