Re: Help SQLLDR Question

tekanary_at_on.bell.ca (Tom Kanary) wrote:

>How does one skip over a field in an input record without
>using the position operator ( I have variable size fields).
 

>For example my input data file

>100.100.100.224 tor4s0 # TOROON02BB1 qwers asdfim
>100.100.101.16 nrb1s0 # NRBAON04BB1 sdfe R1234UI
 

>I would like to insert this input into a table
>with a control file like so:
 

>INTO TABLE xxxxxxxxxxx
>FIELDS TERMINATED BY WHITESPACE
>(
>ip_address CHAR,
>hostname CHAR,
>clli CHAR
>)
 

>How do I skip over the " # ".
>especically if " # " is a " <1 or more fileds > " ?
 

>I don't want to have to filter the data file before loading it
>into oracle.

>--
>_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
>_/ Tom Kanary _/ Email: tekanary_at_on.bell.ca _/
>_/ Bell Sygma _/ TEKANARY _/
>_/ 160 Elgin RM 1950 _/ Phone: 613-781-4685 _/

Try:

LOAD DATA
INFILE *
REPLACE
INTO TABLE hosts
(

ip_address              char terminated by whitespace,
hostname                char terminated by '#' "ltrim(:hostname)",
ccli                    char terminated by whitespace "ltrim(:ccli)"

100.100.100.224         tor4s0  # TOROON02BB1   qwers   asdfim
100.100.101.16          nrb1s0  # NRBAON04BB1   sdfe    R1234UI
 

Use the # to your advantage.... It is the terminator of the second field.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Mon Jul 24 1995 - 00:00:00 CEST