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 Thanks for getting back. I meant STAR_DATA5.CSV, sorry about that. — user3729553
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
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
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

Ben Ben · Accepted Answer · 2014-06-11T12:52:17

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.

Commit point reached

1 Answers