Re: Run command on sqlplus repetitively without reconnection
- From: "David Taft" <oradbt054@xxxxxxxxx>
- To: cichomitiko@xxxxxxxxx
- Date: Fri, 18 May 2007 16:38:46 -0400
Dimitre,
This shell corprocess feature is very cool. Something new for my script
toolbox. Thanks for posting it.
I finally got around to playing with it on AIX 5.2 and found it works with
the Bourne and Korn as well as ksh93 (Enhanced Korn). Below is a script I
based on your posting. The trap I've used works, but gives the following
output after doing a CTL-C.
Error 45 initializing SQL*Plus
Internal error
The shell goes away as well as the Oracle session, but I am wondering if
there is a more graceful way I should be using to shutdown the coprocess
jobs. I couldn't figure it out from googling, nor from the AIX
documentation. It is not a big deal, but any insight you or someone else
may have would be welcomed.
Thanks
David Taft
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#!/bin/sh
# coproc_tst.sh
# query co-process test.
# Runs sqlplus query repeatedly in background without reconnection, but
# displays output to screen.
#
# CTL-C to break (end).
#
# Intercept every signal relevent to the shell except 9 to exit.
trap 'kill %2;exit' 1 2 3 15
sqlplus -s "/ as sysdba"|&
while :
do
print -p "set pagesize 999 linesize 120;"
print -p "col status format a10"
print -p "col EVENT format a30 word_wrapped"
print -p "col sid format 9999"
print -p "col NAME format a25"
print -p "SELECT s.STATUS,w.EVENT,w.STATE,s.SID,w.WAIT_TIME,
w.SECONDS_IN_WAIT \
FROM v\$session_wait w,v\$session s \
WHERE w.sid = s.sid AND s.username != 'SYS' \
AND w.event != 'SQL*Net message from client' \
ORDER BY s.status,w.event,w.state,s.sid;"
sleep 5
done &
while :
do
read -p
echo "$REPLY"
done
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Radoulov, Dimitre"
With ksh93 (and pdksh, on Linux for example) you can use co-processes
Other related posts:
