INCORRECT / TRUNCATED Data Getting Inserted Through Sql Loader

(Doc ID 444442.1)

Last updated on OCTOBER 05, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.3 - Release: 8.1.7 to 10.2
Information in this document applies to any platform.

Symptoms

When trying to load data through sqlloader for a specific column of type CHAR or VARCHAR2 or CLOB, data is getting truncated.

ARTICLE_TEXT is the column in TABLE1 having problem.

SQLLDR command :


sqlldr control=control1.ctl data=articles.csv

 Control file (control.ctl ) :

OPTIONS (SKIP = 1)
LOAD DATA
APPEND
CONTINUEIF NEXT (1:1) <> ','
INTO TABLE TABLE1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(BATCH_NUMBER CHAR,..,ARTICLE_TEXT CHAR(4000),...)


Data for this column in the datafile ( CSV file):

1.6 Omissions - All Work. 
1.7 described in the Specifications 
1.8 Currency


It is getting inserted as:

SQL> select ARTICLE_TEXT from TABLE1;

ARTICLE_TEXT
----------------------------------------------------------------------
1.6 Omissions - All Work..7 described in the Specifications.8 Currency

First character of the remaining lines ( except the first row) getting truncated and also the new line character getting truncated.

Issue is reproducable in 8.1.7.4, 9.2.0.8,10.2.0.2 in all unix platforms. 

The issue is Not Occuring in WINDOWS.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms