Sql Loader Is Ignoring Null Data Columns
Last updated on SEPTEMBER 07, 2015
Applies to:Oracle Database - Enterprise Edition - Version 18.104.22.168 to 22.214.171.124 [Release 7.3.0 to 11.1]
Information in this document applies to any platform.
This problem can occur on any platform.
-- 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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms