SQL Loader; sqlload; CLOB; expected value is NULL instead of empty_clob()
Hi,
We load CLOBs with SQL*Loader. Issue is with loading NULL into CLOB column.
Instead of NULL value CLOB gets empty_clob() as value. (at the same time VARCAHR2 gets NULL value as expected)
Here is test case:
1) create table
CREATE TABLE TEST ( ID NUMBER primary key, C CLOB, COMMENTS varchar2(50));
2) create controlfile: TEST.ctl
LOAD DATA CHARACTERSET UTF8 INFILE 'TEST.dat' "var 8" truncate
INTO TABLE "TEST" FIELDS TERMINATED BY ';' (
"ID" INTEGER EXTERNAL -- NUMBER
, "C" VARCHARC(8,33554432) -- CLOB - NULLIF "C" = BLANKS is tested. It does not help
, "COMMENTS" VARCHARC(3,200) -- VARCHAR2
)
2) create data file: TEST.dat (with unix line ending)
We load CLOBs with SQL*Loader. Issue is with loading NULL into CLOB column.
Instead of NULL value CLOB gets empty_clob() as value. (at the same time VARCAHR2 gets NULL value as expected)
Here is test case:
1) create table
CREATE TABLE TEST ( ID NUMBER primary key, C CLOB, COMMENTS varchar2(50));
2) create controlfile: TEST.ctl
LOAD DATA CHARACTERSET UTF8 INFILE 'TEST.dat' "var 8" truncate
INTO TABLE "TEST" FIELDS TERMINATED BY ';' (
"ID" INTEGER EXTERNAL -- NUMBER
, "C" VARCHARC(8,33554432) -- CLOB - NULLIF "C" = BLANKS is tested. It does not help
, "COMMENTS" VARCHARC(3,200) -- VARCHAR2
)
2) create data file: TEST.dat (with unix line ending)
0