sqlldr and defaultif

According to Ooracle documentation, the defaultif keyword can be used in the   sqlldr control file so a 0 ( zero) is inserted for the numeric column if   the input datafield is null.

Example

 create table t(col1 number, col2 number(10), col3 number)

controlfile

LOAD DATA
INFILE "t.dat"
append INTO TABLE t
FIELDS TERMINATED BY '|'
trailing nullcols
(
col1,
col2 defaultif col2=blanks ,
col3 )

datafile

1||2
2|15|3

sqlplus> truncate table t ;

sqlldr userid=xx/yy control=t.ctl

I expect 0 and 15 should be inserted for col2. However, the values are always null and 15.

If anyone has an example that works for this case, can you please share that. I am using Oracle 9.2.0.2 on AIX 5.1.

Regards

Will Received on Fri Apr 25 2003 - 11:56:09 CDT