My Oracle Support Banner

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

Last updated on MARCH 06, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 7.3.0.0 to 11.1.0.6 [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.

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 <SCHEMA_NAME>.<TABLE_NAME>
FIELDS TERMINATED BY '^'
OPTIONALLY ENCLOSED BY '"'
(
<COLUMN_NAME1>,
<COLUMN_NAME2>,
<COLUMN_NAME3>,
<COLUMN_NAME4>,
DISPLAYNAME nullif DISPLAYNAME = BLANKS,
<COLUMN_NAME5> char(100000),
<COLUMN_NAME6>,
<COLUMN_NAME7>,
<COLUMN_NAME8>,
<COLUMN_NAME9>,
<COLUMN_NAME10>,
<COLUMN_NAME11>,
<COLUMN_NAME12>
)
         
                                                        
                                                 Consecutive Delimiters
Data File                                                    |
                                                             V
LDAP://occ.cian.com^CIAN^LDAP://occ.cian.com^Exchange System^^CN=Domain
         |              |              |             |
         |              |              |             |
         V              |              V             V
    <COLUMN_NAME1>         |           <COLUMN_NAME3>        <COLUMN_NAME34>
                        V
                  <COLUMN_NAME2>

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.

 

Changes

 

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
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.