My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

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

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.