RE: misunderstanding between DEFAULTIF and NULLIF with SQLLOader

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Thu, 04 Oct 2001 07:49:08 -0700
Message-ID: <F001.003A1E1A.20011004073523@fatcity.com>

I have to ask, what's the difference between an empty field and a null field to you?  Empty is the same as null to me - undefined. 

That's the whole reason for the NULLIF keyword.  Make spaces null prior to insert.

You can have sql statements modifying the data prior to insert.  Maybe this will do what you need.  You can't run the load in direct mode if you do this though. 

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
954-935-4117

-----Original Message-----

From:   Maya Kenner [SMTP:maya.kenner_at_trema.com]
Sent:   Thursday, October 04, 2001 10:20 AM
To:     Multiple recipients of list ORACLE-L

Subject:        misunderstanding between DEFAULTIF and NULLIF with SQLLOader

Hello ,
 

I have a small problems because of empty fields and NULL fields.
 

I have a data file extracted from a non-Oracle DB where the empty field is represented by a single space char and NULL field is represented with nothing , ||| is field delimitor , #|# is line delimitor

 

toto||| |||#|#
tutu|||||| #|#
 

select * from mytable

col1    col2     col3
toto                NULL
tutu     NULL    

 

How can I build my control files so that SQL Loader insert a chr(0) if there is a space and a NULL if empty ?
 

Thanks, Maya Received on Thu Oct 04 2001 - 09:49:08 CDT