Re: SQL loader unix script with multiple files

Radoulov, Dimitre schreef:

> <mnemonic01_at_hotmail.com> wrote in message
> news:1167733067.586311.78720_at_a3g2000cwd.googlegroups.com...
> > Hello
> > What I have to doe is create for a large set of control en datafile 1
> > shellscripts which starts te actual load.
> >
> > My problem is :
> >
> > I use variables and check which control files and datafiles are
> > present. That goes well.
> >
> > But what I want is seperate logfiles for each control file so I
> > specified a variable for this to take over then name of the
> > controlfile, but what happens is that only the log file of the last
> > control file is beinig processed.
> > here some code:
> >
> >
> > for LOG_FL in `ls ${DATADIR}/ctl/*.CTL | sed
> > 's/\.[^.]*$//'>${DATADIR}/ctl/filelist.txt |cat
> > ${DATADIR}/ctl/filelist.txt`
> > do
> > export LOG_FL
> > done
> >
> >
> > for file in `ls ${DATADIR}/ctl/*.CTL|sort -rn -k 5,5 | awk '{print
> > $NF}'`
> > do
> > sqlldr $SCHEMA/$SCHEMAPWD control=$file log=$LOG_FL
> >
> >
> > What am I doing wrong here?
>
> You're overwriting the LOG_FL variable value, after the first for loop it
> has the value of the last CTL listed (.[^.]*$ excluded).
> You can write it like this:
>
> for ctl in $(ls "${DATADIR}"/ctl/*.CTL | sort -rn -k 5,5); do
> sqlldr "${SCHEMA}"/"${SCHEMAPWD}" control="$ctl" log="${ctl%.*}"
> done
>
> or like this, if the sort is not relevant:
>
> for ctl in "${DATADIR}"/ctl/*.CTL; do
> sqlldr "${SCHEMA}"/"${SCHEMAPWD}" control="$ctl" log="${ctl%.*}"
> done
>
> You could add a .log extension if you wish, with log="${ctl%.*}.log"
>
>
> Regards
> Dimitre

Ok thanx Dimitre that helped a lot
No I'm facing another challenge

the datafile is with every load another one name= schemaname_"controlfilename_without_ctl"_loaddate.dat so what I'd like to is:
in the sqlloader syntax load the proper datfile belonging to the controlfile, which is available at that moment otherwise about a 100 ctl has to be edited every time a load takes place to specify the infile

thanks Received on Tue Jan 02 2007 - 07:11:38 CST