Hi Tom,

I'm trying to use SQL*Loader to load data into two different tables depending on whether the value in a particular field is NULL or not. My *test* data file (NullTest.dat) looks like this:

"Oak","Tree",20,"metres",1
"Elm","Tree",60,"feet",6
"Ash",,20,"feet",2
"Chestnut","Tree",45,"metres",1

Note that the second field in row 3 is null.

My control file looks like this:

LOAD DATA
INFILE 'NullTest.dat'
REPLACE
INTO TABLE tree_test WHEN tree_type <> BLANKS
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(TREE_NAME CHAR,
TREE_TYPE CHAR,
HEIGHT FLOAT EXTERNAL,
UOM CHAR,
MISC CHAR TERMINATED BY WHITESPACE)
INTO TABLE tree_test_nulls WHEN tree_type = BLANKS
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(TREE_NAME POSITION(1) CHAR,
TREE_TYPE CHAR,
HEIGHT FLOAT EXTERNAL,
UOM CHAR,
MISC CHAR TERMINATED BY WHITESPACE)

When I run SQL*Loader I get 3 records loaded into table tree_test (Oak, Elm and Chestnut) but no records are loaded into table tree_test_null. The log file indicates that record 3 was discarded as it failed all WHEN clauses. The relevant section of the log file follows:

Table TREE_TEST, loaded when TREE_TYPE != BLANKS
Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl Datatype
-------------------- ---------- ----- ---- ---- ---------------------
TREE_NAME FIRST * , O(") CHARACTER
TREE_TYPE NEXT * , O(") CHARACTER
HEIGHT NEXT * , O(") CHARACTER
UOM NEXT * , O(") CHARACTER
MISC NEXT * WHT O(") CHARACTER

Table TREE_TEST_NULLS, loaded when TREE_TYPE = BLANKS
Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl Datatype
-------------------- ---------- ----- ---- ---- ---------------------
TREE_NAME 1 * , O(") CHARACTER
TREE_TYPE NEXT * , O(") CHARACTER
HEIGHT NEXT * , O(") CHARACTER
UOM NEXT * , O(") CHARACTER
MISC NEXT * WHT O(") CHARACTER

Record 3: Discarded - failed all WHEN clauses.

Table TREE_TEST:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
1 Row not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Table TREE_TEST_NULLS:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
4 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

I could convert all occurrences of ,, in the input data file to '," ",' to workaround the problem but I'm unable to edit the *actual* input data file as it contains sensitive accounts information.

How can I get SQL*Loader to correctly detect a null in the field?

Thanks in anticipation...

Neil W. Garside

PS. Love the "one-on-one" book! I've only had it 2 months and it's nearly worn out!