Re: SQL*Loader

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/04/25
Message-ID: <33661e54.27370005@newshost>#1/1

On Fri, 25 Apr 1997 11:14:54 GMT, mwebb_at_cix.compulink.co.uk ("Mark Webb") wrote:

>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.

You can use a .ctl file such as follows. Basically, we'll use SQL functions to process the data as it comes in. We'll map the first N fields (in my example, field1, year, field2). We'll use SQL functions on the year input field to get just the first four characters and if the first 4 characters are 0000, we'll input NULL else we'll input those characters.

We leave month and day as trailing columns since we don't have a delimited input field to map them to (if there happens to be extra fields at the end of the input record, don't worry, for example, my record 1 has extra garbage at the end of the record but we don't ever load it). These will refer back to the year input field and substr out the characters they are interested in, inserting NULL if they are 00, the number otherwise....

LOAD DATA
INFILE *
INTO TABLE "DATA"
REPLACE
FIELDS TERMINATED BY '+'
TRAILING NULLCOLS
(field1,
 year "decode(substr(:year,1,4),'0000',NULL,substr(:year,1,4))",  field2,
 month "decode(substr(:year,5,2),'00',NULL,substr(:year,5,2))",  day "decode(substr(:year,7,2),'00',NULL,substr(:year,7,2))" ) BEGINDATA

a field1 data+19630000+a field2 data+extra garbage we don't care about
b field1 data+19630100+b field2 data
c field1 data+19630121+c field2 data

I sql*plus we can check the results:

SQL> REM so we can easily see 'nulls'
SQL> set null (Null)
SQL> select * from data;
 
FIELD1                YEAR      MONTH        DAY FIELD2
--------------- ---------- ---------- ---------- ---------------
a field1 data         1963 (Null)     (Null)     a field2 data
b field1 data         1963          1 (Null)     b field2 data
c field1 data         1963          1         21 c field2 data
 

SQL> Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Apr 25 1997 - 00:00:00 CDT