Re: SQL loader - SKIP FIELD + ADD DATA

daniel.st-jacques_at_ca.kontron.com (Daniel St-Jacques) wrote in news:2355c0e9.0111161341.129f16db_at_posting.google.com:

> 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

One solution is to simply load the raw data into a "work" table. Then write a small PL/SQL program to accomplish what ever logic is desired or necessary.

HTH & YMMV Received on Fri Nov 16 2001 - 18:48:10 CST