SQL loader - SKIP FIELD + ADD DATA

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 Received on Fri Nov 16 2001 - 15:41:45 CST