shellscript to read data from txt file and import to oracle db

Shell Programming and Scripting
1

Hi all,
Help needed urgently.
I am currently writing a shellscript to read data/record from a flat file (.txt) file, and import/upload the data to oracle database. The script is working fine, but it takes too long time (for 18000 records, it takes around 90 mins).

I guess it takes so long time to insert to db is because the login to db each time after reading one line of record from txt file (sqlplus -s username/password <<EOF)

login to db each time before insert statement because i want switch the environment to oracle db, after insert, i will exit, so that it will point me back to the backend server to get the dir.txt file.

Is this the correct way?
Is there any way/command to use so that no need to switch between environment? or meaning to say, no need to login to db each time when i want to insert a row of data.

Hope somebody can help as i am newbie to shellscript. thx a lot in advance!!!

Below is the codes:
Check if $FILE exists or not

if test ! -f "dir.txt"
then
echo "Error - $FILE not found or mcelog is not configured for 64
bit Linux systems."
exit 1
else
echo "dir.txt file exist"
fi
 
#start for dir.txt
FILENAME='dir.txt'
count=0
cat $FILENAME | while read LINE
do
 
dname=`echo "$LINE" | awk '{ print substr( $0, 0, 44 ) }'|sed "s/\'/\'\'/g"`
dicnum1=`echo "$LINE" | awk '{ print substr( $0, 45, 26 ) }'` 
dposition=`echo "$LINE" | awk '{ print substr( $0, 71, 26 ) }'|sed "s/\'/\'\'/g"`
dcompnm=`echo "$LINE" | awk '{ print substr( $0, 97, 8 ) }'|sed "s/\'/\'\'/g"`
dcpicno=`echo "$LINE" | awk '{ print substr( $0, 105, 14 ) }'`
dcpname=`echo "$LINE" | awk '{ print substr( $0, 119, 46 ) }'|sed "s/\'/\'\'/g"` 
dcprel=`echo "$LINE" | awk '{ print substr( $0, 165, 20 ) }'`
NOW=$(date +"%d/%m/%Y %H:%M:%S")
 
sqlplus -s username/password <<EOF
set scan off;
Insert into T_DIR_TEST (INSERT_DATE,S_NAME,S_NEW_IC,S_POSITION,S_COMPANY,CP_NAME,CP_IC_NO,CP_REL) VALUES (to_date('$NOW','dd/mm/yyyy HH24:MI:SS'),'$dname','$dicnum1','$dposition','$dcompnm','$dcpname','$dcpicno','$dcprel');
Exit
EOF
done
2

Placing sqlplus within while loop wont be a good idea ...

Try with the below modified one ..

[ -f "dir.txt" ] && echo "File present" || exit 1
while read line
do
      << your variable declaration code >>
      echo "<< your full sql insert code >> " >> /your/path/to/insert_query.sql
done < dir.txt
 
##Here connect the DB to insert 
 
sqlplus -s username/password <<EOF
set scan off;
@/your/path/to/insert_query.sql
exit;
EOF