My Oracle Support Banner

INCORRECT / TRUNCATED Data Getting Inserted Through Sql Loader (Doc ID 444442.1)

Last updated on NOVEMBER 25, 2019

Applies to:

Oracle Database - 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.

B is the column in TABLE1 having the problem.

 

SQLLDR command :


sqlldr control=control1.ctl data=<FILENAME>.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
(<COLUMN1> CHAR,...,B CHAR(4000),...)


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

1.1 <VALUE1>
1.2 <VALUE2>
1.3 <VALUE3>


It is getting inserted as:

SQL> select B from TABLE1;

B
----------------------------------------------------------------------
1.1 <VALUE1>.2 <VALUE2>.3 <VALUE3>

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

Issue is reproducible in 8.1.7.4, 9.2.0.8, 10.2.0.2 in all Unix platforms. 

The issue is Not Occurring in WINDOWS.

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.