Re: Run command on sqlplus repetitively without reconnection

You could try 'print -p exit; exit;' in the trap command to exit gracefully
from both SQL*Plus and the shell.

Thanks,

Senthil.

On 5/18/07, David Taft <oradbt054@xxxxxxxxx> wrote:


 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: