#!/bin/ksh
#
# Insert DB user and password here
DB_USER=SCOTT
DB_PASS=TIGER
#
#
DIRECTORY_PATH="$1"
ORACLE_DIRECTORY="$2"
# print $9 may differ from *NIX versions but play with the numbers, this is tested on AIX/RHEL5
#for Debian use print $8
ls -l $DIRECTORY_PATH | grep -v '^d' | awk '{ print $9 }' > $ORACLE_DIRECTORY.lst

#adding files and directory name to csv

for fileName in $(< $ORACLE_DIRECTORY.lst)
do
cat>>$ORACLE_DIRECTORY.csv<<EOF
$fileName,$ORACLE_DIRECTORY
EOF
done

# deleting previously entered files for that directory
#####
cat >> $ORACLE_DIRECTORY.sql <<EOF
begin
	delete directory_files where directory='$ORACLE_DIRECTORY';
	commit;
exception when no_data_found then
	null;
end;
/
exit;
EOF
#
$ORACLE_HOME/bin/sqlplus $DB_USER/$DB_PASS @./$ORACLE_DIRECTORY.sql
#####
#
# loading new files into directory table, using ReadDirectory.ctl control file
####
$ORACLE_HOME/bin/sqlldr userid=$DB_USER/$DB_PASS data=./$ORACLE_DIRECTORY.csv control=./ReadDirectory.ctl log=./ReadDirectory.log
#

## removing files...
rm -rf $ORACLE_DIRECTORY.sql
rm -rf $ORACLE_DIRECTORY.csv
rm -rf $ORACLE_DIRECTORY.lst
# over and out :)
exit