Re: SQL Loader variable length comma delimited file

to skip certain columns, you can use the FILLER keyword in your controlfile. try the following link for more details (or search tahiti.oracle.com):
http://asktom.oracle.com/pls/ask/f?p=4950:8:808238::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:420419456499,%7Bfiller%7D

Cheers,

Bricklen

Tom wrote:
>
> 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??

-- 
 
 
 -----------------------------
* http://pipers.hypermart.net *
 -----------------------------