SQL*Loader (Sqlldr) Translates Blanks into a NULL (Doc ID 365040.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server Enterprise Edition - Version: 8.1.7.1 to 11.1.0.7
This problem can occur on any platform.

Symptoms

You use SQL*Loader to insert data into a table. When the field in the data file contains blanks, NULLs are being inserted for that field within the database. This behavior is visible within the 9i, 10g and 11g versions.

The following illustrates the issue:

Create a table

create table tbl
(
   col1 number,
   col2 varchar2(6)
);


Create input file "tbl.data" with the following entries

1|      | <---there are 6 blank spaces here
2|YYYYYY|
3|XXXXXX|

Create SQL*Loader control file "tbl.ctl"

load data
infile 'tbl.data'
into table tbl
truncate
when(1) != '-'
fields terminated by '|' optionally enclosed by '"'
(
   col1,
   col2
)

Run SQL*Loader

sqlldr scott/tiger control=tbl.ctl

From SQL*Plus check for the NULL

SQL> select * from tbl where col2 is null;

COL1       COL2
---------- ------
         1

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