############sample session#######################################################
# batch file = member
# input filename to be loaded = member.dat
# there two files. member.ctl.base which is like a template with a variable $INPUT
# every time u pass a new file name,a new member.ctl is created, 
# which is used inside the sql*loader.
#################################################################################

bash-2.03$ member member.dat

this is the new control file to be used by sqlloader

load data
infile 'member.dat'
insert into table member
fields terminated by ',' trailing nullcols
(
name,
address
)

SQL*Loader: Release 9.2.0.1.0 - Production on Thu Mar 4 09:57:11 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 5
#######lets try another filename
#######first lets remove the old records
bash-2.03$ sqlplus -s  mag/mag << EOF
> delete from member;
> commit;
> exit
> EOF

3 rows deleted.

Commit complete.

###try again with new filename
bash-2.03$ member member2.dat

this is the new control file to be used by sqlloader

load data
infile 'member2.dat'
insert into table member
fields terminated by ',' trailing nullcols
(
name,
address
)

SQL*Loader: Release 9.2.0.1.0 - Production on Thu Mar 4 09:57:32 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 5

##############these are scripts i used#############

### 
bash-2.03$ cat member
#!/usr/bin/bash
cat member.ctl.base | sed -e "s%$INPUT%'$1'%" > member.ctl
echo
echo this is the new control file to be used by sqlloader
echo
cat member.ctl
sqlldr userid=mag/mag control=member.ctl

#####base control file used as template...############
bash-2.03$ cat member.ctl.base
load data
infile $INPUT
insert into table member
fields terminated by ',' trailing nullcols
(
name,
address
)

load data
..
..
(
col1,
filename constant $input,
col3
)
...

###### first a demo ########
#
#### if give no input values...nothng happens.
#
C:>member
#
#######infile is me.dat sqlldr session is in append mode
#
C:>member me.dat

SQL*Loader: Release 8.1.6.0.0 - Production on Fri Mar 5 16:14:10 2004

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 3

#
#######infile is abc.txt. sqlldr session is in append mode
#
C:>member abc.txt

SQL*Loader: Release 8.1.6.0.0 - Production on Fri Mar 5 16:14:28 2004

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 3

#
##lets check whether the data is been inserted
# 

C:>sqlplus -s mag/mag@mutation
select * from member;

NAME       FILE_ID    ADDRESS
---------- ---------- ----------
Charlie    me.dat     addres1
Peter      me.dat     addres2
Ben        me.dat      address2
name1      abc.txt    addres11
name2      abc.txt    addres21
name3      abc.txt     address21
name1      abc.txt    addres11
name2      abc.txt    addres21
name3      abc.txt     address21

9 rows selected.

exit
#
#####this is batch file which creates the member.ctl
#
C:>cat member.bat
@echo off
:loop
if "%1"=="" GOTO continue
       echo load data                          >member.ctl
       echo infile '%1'                        >>member.ctl
       echo append into table member           >>member.ctl
       echo fields terminated by ','           >>member.ctl
       echo (                                  >>member.ctl
       echo name,                              >>member.ctl
       echo file_id constant '%1',     >>member.ctl
       echo address                            >>member.ctl
       echo )                                  >>member.ctl
       sqlldr userid=mag/mag@mutation control=member.ctl
       :End
SHIFT
GOTO Loop
:continue

C:>cat member.ctl
load data
infile 'abc.txt'
append into table member
fields terminated by ','
(
name,
file_id constant 'abc.txt',
address
)