Re: SQL Loader variable length comma delimited file

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3CDC06E2.7D8C5A8F_at_exesolutions.com>...
> Tom wrote:
>
> > Hi ,
> > I was wondering if anyone could tell me how to specify the position
> > when inserting variable length comma delimited records into multiple
> > tables. The Oracle documentation does not seem to cover this.
> > Because the positions of fields are relative if inserting into just
> > one table you would just do somthing like:
> >
> > LOAD DATA
> > INFILE'data04/ORACLE/TEST/prod'
> > BADFILE'prod.bad'
> > INSERT INTO TABLE pord'
> > FIELDS TERMINATED BY','OPTIONALLY ENCLOSED BY ""
> > trailing nullcols
> > (
> >
> > PROD_CODE,
> > PROD_DESCR,
> > PROD_CLASS,
> > PROD MKTG_CODE
> > terminated by whitespace)
> >
> > All the examples for Multiple tables seem to use a start and end
> > position as if it assumes that the records are fixed length e.g.
> >
> > dept no POISITION (1:2) integer external
> >
> > So what is to be done when the fields are of variable lenght, comma
> > delimited and are to be inserted into multiple tables. I think i
> > cannot use relative positioning as their position in the file is not
> > the same as i would like to insert them into the tables (also there
> > are fields in the file which i do not wish to insert at all). I cannot
> > use start and end positions because the record is variable length??
> > Any suggestions appreciated...
>
> In delimited records position is irrelevant and not specified. That is the
> point of the delimiter.
>
> Daniel Morgan

Okay,
So if the records are inserted relatively how do i skip fields in my record
E.g my record looks like
Tom,Tumb,Developer,2,4,35,M

and i insert
INSERT INTO TABLE employee
FIELDS TERMINATED BY','OPTIONALLY ENCLOSED BY "" (
 FIRSTNAME,
 LASTNAME
)
INTO TABLE EMPLOYEE_SKILLS
FIELDS TERMINATED BY','OPTIONALLY ENCLOSED BY "" (
 JOBTITLE
 LEVEL
)
INTO TABLE EMPLOYEE
FIELDS TERMINATED BY','OPTIONALLY ENCLOSED BY "" (
 AGE,
 SEX
)

From the above exampe you can see that there is one field more in the record that which i insert for. That field is the number four which i want to exclude. How would that be done. Because i think in the above case 4 will be saved as age rather than 35 and sex will then be set to 35?? Received on Mon May 13 2002 - 10:12:24 CEST