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_at_gmail.com> 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
>

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