Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL*Loader

Re: SQL*Loader

From: Dragon Fly <dfly_at_siberia.ru>
Date: 1997/04/25
Message-ID: <01bc51ae$1590c5e0$144107d0@p2390>#1/1

"Mark Webb" <mwebb_at_cix.compulink.co.uk> wrote in article <E96xwu.EpM_at_cix.compulink.co.uk>...
> Hi everyone,
>
> I'm new to SQL*Loader. I've been presented with a
> data file to load and I'm having problems with a date
> field. For a date of birth sometimes the year is known
> but the month and day may not be. This has been
> represented in the source file as (for example) 19630000.
> The file has fields delimited by a plus (+) sign. The
> decision has been taken to split the date into year,month
> and day integers each of which can be null.
>
> My quest is, what do I need to put in my SQL*Loader
> control file to persuade it to read a single delimited
> field, split it into 3 actual columns and substitute NULLs
> in the even that any of the 3 columns contain all zeros?
> Or is this too much to ask? Any help *gratefully*
> received. Thanks.
>
> Mark W.

assuming 19630000 starts at position, eg. 33. then your control file might look like

YEAR		POSITION(33,36)	INTEGER EXTERNAL 	NULLIF  YEAR='0000',
MONTH		POSITION(37,38) 	INTEGER EXTERNAL 	NULLIF  MONTH='00',
DAY		POSITION(39,40)	INTEGER EXTERNAL	NULLIF  DAT='00',

etc

Cordially,
Serge

Send your humble donations to starving DBAs of Siberia. $120,000/year minimum contribution   Received on Fri Apr 25 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US