12.2 SQL*Loader: EMPTY_LOBS_ARE_NULL Parameter Is Introduced To Set LOB Data To NULL Rather Than To An Empty_CLOB() (Doc ID 2205952.1)

Last updated on MAY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

Symptoms

SQL*Loader inserts Empty_CLOB() instead of a NULL in the database column when the data is null in the input file.

Example:
========

$ sqlplus tc/tc

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.2.0 - 64bit Beta

SQL> create table t
(
c0 varchar2(10),
c1 clob );

Table created.

ctl1.ctl
==========
load data
infile *
truncate
into table t
fields terminated by ','
trailing nullcols
(
c0 char,
c1 char
)
begindata
1,,

bash-4.1$ sqlldr tc/tc control=ctl1.ctl

SQL*Loader: Release 12.2.0.2.0 - Beta on Mon Nov 21 05:04:53 2016

Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 1

Table TC.T:
1 Row successfully loaded.

Check the log file:
ctl1.log
for more information about the load.

SQL> connect tc/tc
Connected.
SQL> SELECT c0,c1
FROM t
WHERE DBMS_LOB.compare (c1, EMPTY_CLOB ()) = 0;

C0 C1
---------- --------------------------------------------------------------------------------
1

 

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