Re: sqlldr and defaultif

"will Chakraborty" <uchakraborty_at_manh.com> wrote in message news:e9fbcf8a.0304250856.62eb97a4_at_posting.google.com...
> 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

For some reason in this case DEFAULTIF behaves differently than expected, the only way to work around the problem is to add the SQL operator "NVL(:col2, 0)", in which case the DEFAULTIF clause becomes totally useless. Note also that, according to the Utilities manual, the lack of a datatype specification like INTEGER EXTERNAL should explain why you're getting nulls instead of zeroes, but oddly enough, even specifying INTEGER EXTERNAL or FLOAT EXTERNAL does not solve the problem.

Note also that the workaround prevents you from using the direct path load, resulting in slower performance.

I am going to investigate this a little bit further.

Bye,
Flavio Received on Fri Apr 25 2003 - 12:42:00 CDT