Commit point reached

Question

Hi I'm trying to load some data in to an Oracle Table I created

Here's the table I created in Vivek schema

Desc STAR

Name                       NULL       TYPE
-----------------------------------------------------
STAR_ID                    Not Null    Number(4)
FIRST_NAME                             Varchar2(30)
LAST_NAME                              Varchar2(30)
DOB                                    Date
SEX                                    Char(1)
Nationality                            Varchar2(40)
Alive                                  Char(1)

Following is the data in the STAR5.CSV file I am trying to upload using SQL Loader

10,NASEERUDDIN,SHAH,M,INDIAN,Y
11,DIMPLE,KAPADIA,F,INDIAN,Y

The control file is as follows

load data
 infile '/home/oracle/host/Vivek12/STAR_DATA5.csv'
append
into table vivek.STAR
fields terminated by "," 
( STAR_ID,FIRST_NAME,LAST_NAME,SEX,NATIONALITY,ALIVE )

When I run the SQL Loader with the following command

$ sqlldr vivek/password
control = /home/oracle/sqlldr_add_new.ct1

I get the message:

Commit point reached - logical record count 2

However the data is not loaded and the are put in the file STAR5.bad

Any idea why the data isn't getting loaded?

The log file will tell you why the records were rejected. What does that say? You've referred to STAR5.CSV in the question but the control file it looking for STAR_DATA5.csv; are your file names consistent? — Alex Poole
– Alex Poole, Commented Jun 11, 2014 at 11:26
Alex Thanks for getting back. I meant STAR_DATA5.CSV, sorry about that. — user3729553
– user3729553, Commented Jun 11, 2014 at 12:30
Alex Just had a look at the log file, it says the data for the field 'Alive' was too big. Probably a space has got added. Following is the text of the log file ( Thanks for your help) — user3729553
– user3729553, Commented Jun 11, 2014 at 12:40
Record 2: Rejected - Error on table VIVEK.STAR, column ALIVE. ORA-12899: value too large for column "VIVEK"."STAR"."ALIVE" (actual: 2, maximum: 1) 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Total logical records skipped: 0 Total logical records read: 2 Total logical records rejected: 2 Total logical records discarded: 0 Elapsed time was: 00:00:30.48 CPU time was: 00:00:00.01 — user3729553
– user3729553, Commented Jun 11, 2014 at 12:41
It was to do with the last column. When I took off the last field in the data and modified the control file to remove the last column in the list of fields, I was able to load the data. Alex Thanks very much for your help. Reading the log file helped — user3729553
– user3729553, Commented Jun 11, 2014 at 13:00

Ben · Accepted Answer · 2014-06-11 12:52:17Z

3

You most likely have either an "invisible" character at the end of your line. Perhaps you're executing this on Linux and the file was created on Windows so you've got an extra carriage return - Linux only uses line-feed as the line terminator.

Change your ctl file to remove terminating whitespace:

load data
infile '/home/oracle/host/Vivek12/STAR_DATA5.csv'
append
  into table vivek.STAR
fields terminated by "," 
 ( STAR_ID
 , FIRST_NAME
 , LAST_NAME
 , SEX
 , NATIONALITY
 , ALIVE terminated by whitespace
   )

If this doesn't work, then you're going to need to work out what characters are there and replace them.

answered Jun 11, 2014 at 12:52

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3729553 Over a year ago

Ben, Thanks very much. I'll try this out and update this chain

user3729553 Over a year ago

You are right though , I am creating the file on Windows using excel , saving it as a .csv file and SFTPint it to Linux.

user3729553 Over a year ago

Ben, It worked perfectly when I added 'terminated by whitespace'. Thanks very much for your help

Collectives™ on Stack Overflow

Commit point reached

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related