Sql Loader Is Ignoring Null Data Columns
(Doc ID 751541.1)
Last updated on MARCH 06, 2019
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 to 188.8.131.52 [Release 7.3.0 to 11.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
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.
OPTIONS (ERRORS=999, SKIP=1)
INFILE '<File Path>'
APPEND INTO TABLE <SCHEMA_NAME>.<TABLE_NAME>
FIELDS TERMINATED BY '^'
OPTIONALLY ENCLOSED BY '"'
DISPLAYNAME nullif DISPLAYNAME = BLANKS,
Data File |
| | | |
| | | |
V | V V
<COLUMN_NAME1> | <COLUMN_NAME3> <COLUMN_NAME34>
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