--
-- before load
--
oracle@mutation#count mutation scott.dept

Table:scott.dept

  COUNT(*)
----------
         0
--
-- check the datafile to be loaded. This file has 70 lines
--

oracle@mutation#wc -l dept.dat
      70 dept.dat

--
-- This is the script that takes filename to be loaded as input.
--
oracle@mutation#split_load dept.dat
input file is dept.dat
Loading dept.dat00

SQL*Loader: Release 9.2.0.4.0 - Production on Tue Jan 10 07:34:48 2006

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

Commit point reached - logical record count 19
Loading dept.dat01

SQL*Loader: Release 9.2.0.4.0 - Production on Tue Jan 10 07:34:48 2006

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

Commit point reached - logical record count 20
Loading dept.dat02

SQL*Loader: Release 9.2.0.4.0 - Production on Tue Jan 10 07:34:48 2006

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

Commit point reached - logical record count 20
Loading dept.dat03

SQL*Loader: Release 9.2.0.4.0 - Production on Tue Jan 10 07:34:49 2006

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

Commit point reached - logical record count 11

--
-- After load
--
oracle@mutation#count mutation scott.dept

Table:scott.dept

  COUNT(*)
----------
        70

--
-- This is the script
--

oracle@mutation#cat split_load
#split your input file identified by $1.
#i am splitting every 20 lines of a 70 line textfile
#output files are prefixed by filename.extension.
csplit -k -f $1 $1 20 {19} > /dev/null 2>&1
#to be in safe side, i am moving the original file to something else for future usage
mv $1 backup_$1.moved
echo input file is $1
#now browing through every split file created and loading those files one by one
#to make sqlldr accept an input filename, i am using a template controlfile.
#for every load i pass a new value for the input file ( the file split)
#and create a new control file and use that file. So the template stays as is.
#during sqlload i am using errors=0, so that, even if ONE record fails, the whole
#load / file is aborted. We can comeback and load that file later.
ls -lt $1* | awk '{print $9}'| while read split_filename
do
        echo "Loading $split_filename"
        sed s/FILENAME/$split_filename/ template.ctl > /tmp/dept.ctl
        sqlldr userid=scott/tiger control=/tmp/dept.ctl errors=0
done

--
--The template i used.
--
oracle@mutation#cat template.ctl
LOAD DATA
infile 'FILENAME'
append
INTO TABLE dept
FIELDS TERMINATED BY ","
trailing nullcols
(
deptno ,
dname,
loc
)