RE: sql*loader default value

From: Witold Iwaniec <wiwaniec_at_novalistech.com>
Date: Tue, 03 Apr 2001 09:57:03 -0700
Message-ID: <F001.002DFA9F.20010403082052@fatcity.com>

Sorry I misunderstood your request. Try something like this:

THE_VAL POSITION(111:120) DECIMAL EXTERNAL "NVL(:THE_VAL, 33)" It works for me - inserts 33 into numeric column when the datafile contains spaces or nothing (if it is the last field)

HTH Witold

On 3 Apr 2001, at 7:20, gregory.t.norris_at_mail.sprint. wrote:

> Thanx to Witold and Prakash for suggesting NULLIF and DEFAULTIF.
> Unfortunately, it looks like both of those do basically the opposite of
> what I'm trying to accomplish. Essentially, I want to prevent
> sql*loader from ever inserting NULL in a column (which may not be
> numeric), substituting whatever value is appropriate for the given
> application instead. Something like using "nvl(:field,'unknown')" in
> the field specification of the control file for conventional path loads.
>
> As far as I can see, there's no way to do this within sql*loader
> itself. So the choices would be to run a cleanup just after the load
> (which may or may not negate the speed benefits of using direct path),
> or to somehow pre-process the data file to fill in the missing values
> (perhaps an awk or perl script). Of course, if I'm missing something
> obvious please let me know... the sql*loader documentation seems
> designed to confuse. (-:
>
> Cheers!
>

Witold Iwaniec
Senior Software Developer
NovaLIS Technologies
wiwaniec_at_novalistech.com
http://www.novalistech.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Witold Iwaniec
  INET: wiwaniec_at_novalistech.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Apr 03 2001 - 11:57:03 CDT