Re: Run command on sqlplus repetitively without reconnection

From: David Taft <oradbt054_at_gmail.com>
Date: Fri, 18 May 2007 16:38:46 -0400
Message-ID: <69b058e80705181338o4ba39d0i7ec62e1ea3ebc804@mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 18 2007 - 15:38:46 CDT