NULL behavior in ORACLE vs SYBASE[SUMMARY]

In my original post I said:

> I need a way of quickly and easily changing all NULLS to empty string
> by default on insert. I could do this using DB triggers but that seems
> a bit ugly since I would have to have a trigger for every CHAR column
> in the SCHEMA. All the other options I can think of are equally scary.
> Rewriting the statements to take NULLs into account is not an option
> right now.

Looking back I think I was hoping for some sort of magic to change NULL behavior. (An init.ora param called _HANDLE_NULLS_THE_WAY_LEE_WANTS_TO=TRUE would have been nice :) This was of course unrealistic.

Several people pointed me towards the default clause of the CREATE TABLE statement. This is a reasonable idea but doesn't work in all cases. Specifically, we load quite a lot of this data via sqlldr which emperically tries to insert a default value of NULL if you have no data in the column. Where a trigger would actively change a NULL to ' ', the DEFAULT clause only works if you load no value.

Our most likely solution will be to add a NVL function to the control file for every column we care about. The drawback here is it prevents us from using the DIRECT LOAD and UNRECOVERABLE options of sqlldr.

Finally, we have some hope for the PRESERVE BLANKS loader key word which should allow us to load spaces instead of nulls. This ofcourse requires that the original datafile has spaces to preserve.

Thanx to the following for responding:

Erwin Dondorp    <erwin_at_pcssdc.pttnwb.nl>
Gary Gapinski    <gapinski_at_lerc.nasa.gov>
Johan van Streun <jstreun_at_nl.oracle.com> 
Laurence Barea   <larry_at_tarik.demon.co.uk>
Roy Wagner       <roywagner_at_aol.com>

-- 
Regards, 

Lee E. Parsons                  		
Systems Oracle DBA	 			lparsons_at_world.std.com