Running multiple parallel sqlplus connections for a certain period of time in bash

Question

I have this code that creates 20 parallel sqlplus instances, doing some queries and exits:

   #!/bin/sh
    for i in $(seq 1 20);
    do
       echo "CREATE TABLE table_$i (id NUMBER NOT NULL);
       select * from table_$i;
       ! sleep 30
       select * from table_$i;
       ! sleep 30
       DROP TABLE table_$i;" | sqlplus system/password &
    done
    wait

I need to adjust this code if possible so it would run for an hour with the following conditions:

Always stay on 20 connections, if one sqlplus instance is closed (Finished it's process) another one should open, i need to maintain a certain amount of connections for X amount of time.

Is there anything i can add to this code that will achieve what i need?

Make a function that takes `$i` as an argument and in that function start with `date` and use `while` loop with sqlplus running until hour is finished. Call this function 20 times with `&` and wait. — Walter A, Nov 25 '19 at 08:10
Can you please show me a code example and explain how does it work? — totothegreat, Nov 25 '19 at 13:44

score 1 · Accepted Answer · answered Nov 26 '19 at 21:19

For looping during an hour, see https://stackoverflow.com/a/22735757/3220113

runsql() {
   i="$1"
   end=$((SECONDS+3600))
   SECONDS=0
   while (( SECONDS < end )); do
      # Do what you want.
      echo "CREATE TABLE table_$i (id NUMBER NOT NULL);
            select * from table_$i;
            ! sleep 30
            select * from table_$i;
            ! sleep 30
            DROP TABLE table_$i;" | sqlplus system/password 
      sleep 1 # precaution when sqlplus fails, maybe wrong password
   done
}

for i in $(seq 1 20); do
   runsql $i &
done
wait

Explanation:
The main loop at the bottom starts the function runsql 20 times in the background.
The function runsql could use $1 everywhere, I copy it to i for code that looks like the original.
SECONDS is a counter that is changed every second by the shell, so we do not need to call date.
3600 is an hour.
Inside (( .. )) you can do math without $ in front of variables.

following issue regarding this script: https://stackoverflow.com/questions/59249079/executing-bash-script-via-java-in-background-after-ssh-connection-is-closed — totothegreat, Dec 09 '19 at 13:08

Running multiple parallel sqlplus connections for a certain period of time in bash

1 Answers1