Re: Run command on sqlplus repetitively without reconnection

  • From: "Philip Douglass" <philipsd@xxxxxxxxx>
  • To: "Wolfson Larry - lwolfs" <lawrence.wolfson@xxxxxxxxxx>
  • Date: Wed, 23 May 2007 20:23:16 -0400

From the ksh man page:
    <&p   The input from the co-process  is  moved  to  standard input.

So, what this is doing is starting up a cat with the co-process attached to
its STDIN, so that the output the co-process produces is output to the

The sqlplus arg is a plain forward slash because I have an OS authenticated
(OPS$) account. If you use /nolog then you will need to have the
authentication embedded in the script, something I prefer not to do when
possible, especially for DBA accounts (like mine!)

as for:
       print -p -- '/'
I expect that when a single sql statement is run, a forward slash will
execute it again, and again, and again... If the script you are executing
has more than one statement that you want to loop over, then it would be
appropriate to change that to @$1 of course.

On 5/23/07, Wolfson Larry - lwolfs <lawrence.wolfson@xxxxxxxxxx> wrote:


   1)  Can you explain this syntax, that is exactly what is happening
cat <& p &

    2) to get this to work on Solaris I had to change this line
sqlplus -s / |& # Open a pipe to SQL*Plus
sqlplus -s /nolog  |& # Open a pipe to SQL*Plus

    Larry Wolfson

*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jared Still
*Sent:* Wednesday, May 23, 2007 1:23 PM
*To:* philipsd@xxxxxxxxx
*Cc:* oradbt054@xxxxxxxxx; cichomitiko@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Run command on sqlplus repetitively without reconnection

On 5/22/07, Philip Douglass <philipsd@xxxxxxxxx> wrote:
> Here's what I've been using for awhile now to accomplish the repetitive
> monitoring script in SQL*Plus pattern:
> #!/bin/ksh
> sqlplus -s / |& # Open a pipe to SQL*Plus
> cat <& p &
> print -p -- "exec dbms_application_info.set_client_info(client_info =>
> '${USER}@${HOSTNAME}');"
> print -p -- "exec dbms_application_info.set_module(module_name =>
> '$(basename $0)', action_name => '$1');"
> print -p -- "@$1"
> while (true); do
>         sleep $2
>         print -p -- '/'
> done
Thanks, this is clever.
Just now I have a use for it. :)

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.

Other related posts: