CODEWITHSUNDEEP
oracle-databaseshellunixsqlplus
alejomarchan
alejomarchan

Reputation: 368

Execution message Oracle sqlplus from Unix

I have a process that connects to Oracle sqlplus from a shell that requests a password and then start the execution of Store Procedure in the following way:

echo -e "Enter Password"
read -s pass

$ORACLE_HOME/bin/sqlplus -s -l /nolog << EOF >> $log_deploy
connect $schema/$pass@$db
@$sql/main.sql;
EOF

The fact is that this process has a variable duration and it is executed from the console of the server by an operator.

There is some way of notifying the operator every 10 minutes for example "The process continues in execution"? Since once inside the sqlplus remains in it until the execution of sp ends and returns to the console.

Any suggestions or idea?

Upvotes: 3

Views: 196

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Put the sqlplus heredoc in background. Wait for completion of last background process id ($!) and sleep for some amount of seconds by checking its completion (10 mins is a long interval by the way )

intrvl=100
sqlplus  <<EOF &
input
EOF
PID=$!  #get the pid of the last background process.

while [ 1 ]
do
if ps -p $PID > /dev/null
 then
   echo "The process continues in execution"
   sleep $intrvl
 else 
   break
 fi
done

Having said that a good approach should be to keep the monitoring part out of the processing code.

Upvotes: 7

Related Questions