Connect once db disconnect after all execution

Shell Programming and Scripting
1

Hi All,

Please see the below code.

it is working fine when in 'test_file' have only one emplid.

test_file contains only emplid and date, like below ...
0000221|1/12/2003
0000223|1/1/1996

Problem :-
when test_file contains more then one records(emplids) it is not giving any errors but it is disconnecting from database and
always connecting to database for each and every emplid..
But i want once connect to data base and after perform all execution it would disconnect from data base.
Please help me on this problem.

#!/bin/sh
set -x
echo "test for multiple values"
UIDPSWD=`cat /projects/feeds/twest/uidpswd`
echo "oracle connection test"
full=/projects/feeds/twest/test_file
#values=`cut -d'|' -f1 $full|sed -e "s/.*/'&'/" -e 's/$/;/g' -e '$s/,$//'`
#below command will work in bourne shell
#values=(`awk -F"|" '{print $1}' $full`)
#below command will work in korn shell
set -A values $(awk -F"|" '{print $1}' $full)
for i in ${values
[*]}
do
sqlplus -s $UIDPSWD <<EOF >>/projects/feeds/twest/result.txt
set serveroutput on
set verify off
set heading off
set feedback off
set ECHO OFF
--spool /projects/feeds/twest/ouput_file.txt
declare
cursor c1
is select emplid,ACTION,ACTION_DT,ACTION_REASON
from ps_job
where emplid='$values';
c2 ps_job%rowtype;
begin
DBMS_OUTPUT.PUT_LINE( 'emplid'|| chr(9) ||'ACTION'|| chr(9) ||'ACTION_DT'|| chr(9) ||'ACTION_REASON');
for i in c1 loop
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(i.emplid|| chr(9) ||i.action||chr(9)||i.action_dt||chr(9)||i.ACTION_REASON);
end loop;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('test Update query: ' || SQLERRM);
end;
/
exit;
EOF
done

Thanks & Regards,
Krupa

2

You can define a variable with list of empids separated by comma:

while IFS="|" read empid skip
do
        [ -z "$qid" ] && qid="$empid" || qid="${qid},${empid}"

done < test_file

And use that in your cursor:

cursor c1
is select emplid,ACTION,ACTION_DT,ACTION_REASON
from ps_job
where emplid in ('$qid');
3

can you please explain about this code ,actually i didn't get

Thanks,
Krupa

4 
# Set IFS (Internal Field Separator) to pipe |
# Read first field into variable: empid rest of fields into: skip
while IFS="|" read empid skip
do
        # If variable qid is empty, assign empid value to qid
        # If variable qid is not empty, append empid value to qid separated by comma
        [ -z "$qid" ] && qid="$empid" || qid="${qid},${empid}"

done < test_file