Sql Loader Is Ignoring Null Data Columns (Doc ID 751541.1)

Last updated on SEPTEMBER 07, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 7.3.0.0 to 11.1.0.6 [Release 7.3.0 to 11.1]
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms

-- Problem Statement:
Using SQL* Loader to load data and SQL*Loader does not recognize NULL values coming from the data file .

SQL*Loader is loading the data from the field after the consecutive delimiters into the column the NULL value should be loaded into.

Control file 
------------ 

OPTIONS (ERRORS=999, SKIP=1) 
LOAD DATA 
INFILE '<File Path>' 
APPEND INTO TABLE CIRCWS.AD_USER_GROUPS 
FIELDS TERMINATED BY '^' 
OPTIONALLY ENCLOSED BY '"' 
( 
OBJECT_PATH, 
OBJECT_NAME, 
ADSPATH, 
NAME, 
DISPLAYNAME nullif DISPLAYNAME = BLANKS, 
MEMBEROF char(100000), 
MEMBER, 
DISTINGUISHEDNAME, 
CN, 
DESCRIPTION, 
GROUPTYPE, 
SAMACCOUNTNAME, 
IS_PRIMARY 
) 
         
                                                        
                                                 Consecutive Delimiters 
Data File                                                    | 
                                                             V 
LDAP://occ.cian.com^CIAN^LDAP://occ.cian.com^Exchange System^^CN=Domain 
         |              |              |             | 
         |              |              |             | 
         V              |              V             V 
    OBJECT_PATH         |           ADSPATH        NAME 
                        V 
                  OBJECT_NAME 

It starts by reading the data and as soon as it comes to a terminator, it knows that data goes to the corresponding column as defined in the control file.

When it comes to consecutive terminators ^^ instead of reading the zero-length string between them as a NULL it continues on and it starts to load the data for the next column into the column where the NULL should have been loaded into.

 

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