Sql Loader Is Ignoring Null Data Columns
(Doc ID 751541.1)
Last updated on AUGUST 04, 2018
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 to 220.127.116.11 [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.
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