Re: SQL loader - SKIP FIELD + ADD DATA

In article <2355c0e9.0111161341.129f16db_at_posting.google.com>, daniel.st-jacques_at_ca.kontron.com says...
>
>Hello everyone
>
>I am using Oracle 7.3.2.2
>
>I do have an ascii file to load that is look like:
>
>number|name|address1|address2|city|zip
>
>In the Oracle table I have several field that are mandatory but not
>present into the ascii file.
>Oracle table: cust_id
> name
> address
> city
> zip
> can-phone
> can-write
>
>1. Is it possible with SQL loader 7.3.4.4.1 to skip a field from the
>ascii file?
> in this example "address2"
>
>2. Is it possible to force data into some field even if the data is
>not part of the ascii file?
> example : can-phone = "Y"
> can-write = "N"
>
>Here is my control file
>LOAD DATA
>INFILE 'asciifile' BADFILE 'asciifile.bad'
>INTO TABLE customer
> FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
> ( cust_num,
> name "UPPER( :name )",
> address "UPPER( :address )"
>----> Something missing to skip address2. FILLER doesn't work
> city "UPPER( :city )"
> zip "UPPER( :zip )"
> )
>
>There is 2 things missing in the control file.
> 1. How to skip a field
> 2. How to add fields with fix data that is not part of the ASCII
>file.
> If the fields that are not part of the ascii file can be filled
>up
> by condition it would be great.
> example: if zip begins by 1
> then can-phone = "Y"
> can-write = "N"
> else can-phone = "N"
> can-write = "Y"
>
>Is anyone can find an EASY way to do this.
>
>That has to by done each week.
>
>I am looking forward for the answers
>
>Daniel St-Jacques
>daniel.st-jacques_at_ca.kontron.com

LOAD DATA
INFILE * BADFILE 'asciifile.bad'
INTO TABLE t
replace
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' trailing nullcols
( cust_id,
  name "UPPER( :name )",
  address "UPPER( :address )",
  can_phone "decode( substr(:zip,1,1), '1', 'Y', 'N' )",   city "UPPER( :city )",
  zip "UPPER( :zip )",
  can_write "decode( substr(:zip,1,1), '1', 'N', 'Y' )" )
begindata
1234|name|address1|address2|city|12345
5678|name|address1|address2|city|23456

We can use the fact that you want to skip address2 to stuff "can_phone" in there. can_phone will get address2 but -- we'll apply a sql function to override that fact and populate can_phone with a decode on zip.

Later, since we added trailing nullcols to the spec -- we'll load can_write using the same technique. Instead of loading NULLS as trailing nullcols would normally, we'll apply a function to zip again and get the data.

After loading, we find:

ops$ora734_at_ORA734.WORLD> l
  1 select cust_id, zip, can_phone, can_write   2* from t
ops$ora734_at_ORA734.WORLD> /

   CUST_ID ZIP   CAN_PHONE                 CAN_WRITE
---------- ----- ------------------------- -------------------------
      1234 12345 Y                         N
      5678 23456 N                         Y

as you want.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp