Run only 3 sqlplus session at a time

UNIX for Advanced & Expert Users
1

Below code runs n number of session at a time when called. I want to restrict it to run only for 3 sqlplus at a time. Is there a way to do the same.

for name in `cat abc.txt`
do
(sqlplus -s  usert/password@host <<-EOF>> error.txt
			SET LINESIZE 1000
			select '$name', TO_CHAR(current_timestamp(0),'DD-Mon-YYYY HH24:MI:SS') as start_time from dual;
			EXEC proc('$name');
			select '$name' ,TO_CHAR(current_timestamp(0),'DD-Mon-YYYY HH24:MI:SS') as end_time from dual;
			EOF
 ) &
done
wait

run the below part only if all the loops in the for has completed!
echo "Complete"
2 
count=0
for name in `cat abc.txt` do (sqlplus -s  usert/password@host <<-EOF>> error.txt
             SET LINESIZE 1000
             select '$name', TO_CHAR(current_timestamp(0),'DD-Mon-YYYY HH24:MI:SS') as start_time from dual;
             EXEC proc('$name');
             select '$name' ,TO_CHAR(current_timestamp(0),'DD-Mon-YYYY HH24:MI:SS') as end_time from dual;
             EOF  ) &
let count=$count+1
if [ $count -gt 2 ]
then
   wait
   count=0
fi
 done 
wait 
run the below part only if all the loops in the for has completed! echo "Complete"
3

That for loop is basically wrong: Useless Use of Backticks

The right way is to use a while loop.

Here is another approach using pgrep to count the number of child sqlplus

#!/bin/ksh

while read name
do
        sqlplus -s usert/password@host <<EOF &
        set linesize 1000
        SELECT '$name', To_char(CURRENT_TIMESTAMP(0),'DD-Mon-YYYY HH24:MI:SS') AS start_time FROM dual;
        EXEC PROC ('$name');
        SELECT '$name', To_char(CURRENT_TIMESTAMP(0),'DD-Mon-YYYY HH24:MI:SS') AS end_time FROM dual;
        exit
EOF
        [ "$( pgrep -P $$ -l sqlplus | wc -l )" -eq 3 ] && wait

done < abc.txt
4

The wait waits for all jobs to finish.
If your jobs have a different run time and you want to keep the running jobs near the allowed maximum, then count them with the jobs command

parallel=3
while read name
do
  echo "running $name"
  ( 
    sqlplus ...
  ) &
  while [ `jobs | wc -l` -ge $parallel ]; do  sleep 2; done
done < abc.txt
2 Likes
5 
sqlplus.sh
---------

sqlplus -s  usert/password@host <<-EOF>> error.txt
			SET LINESIZE 1000
			select '$name', TO_CHAR(current_timestamp(0),'DD-Mon-YYYY HH24:MI:SS') as start_time from dual;
			EXEC proc('$name');
			select '$name' ,TO_CHAR(current_timestamp(0),'DD-Mon-YYYY HH24:MI:SS') as end_time from dual;
			EOF
			
--------------			
while read name
do
(sh sqlplus.sh) &
	while [ $(jobs -l | wc -l) -ge 3 ]
	do
		echo "";
		sleep 120;
	done
done < abc.txt
wait

echo "Complete"

By doing the above check condition it makes sure only 2 process runs at a time and will wait for the specified time given and then check again and process it.