Re: DBMS_Alert Implementation - Help

From: Don Smith <eddas_at_huber.com>
Date: 1996/03/15
Message-ID: <4ic0q1$d89_at_muddy.huber.com>#1/1

In article <4ia4ab$1vu_at_news.Belgium.EU.net>, ghp_at_infosoft.be (Gerard H. Pille) writes:

|> A little trick I believe I invented (I haven't seen anybody else use it):
|>
|> Use sqplus as a co-process in unix. The unix script goes like this:
|>
|> #!/bin/ksh
|>
|> # Start sqlplus as a co-process
|> sqlplus -s user/password |&
|>
|> print -p "start dbms_waitone" # don't exit in dbms_waitone
|> # just signal every alert you receive
|>
|> while read -p # will hang untill co-process gives some output
|> do
|> # the necessary unix thingies
|> mail badri <<-EOF
|> Dear Badri,
|> Would you be so kind as to look into the alert I
|> received?
|> I received $REPLY # read -p -> REPLY
|> Thank you very much.
|> Your alert-server.
|> EOF
|> done # this will never be
|> # unless you provide an exit within the loop
|> # then:
|> print -p "exit;"
|> wait # for the co-process (child) to finish
|> exit
|>
|> You can do beautifull things with sqlplus as a co-process, you need to
|> know some unix though. Anything goes!
|> --
|> Kind reGards
|> \ / |
|> X |
|> / \ s
|> Gerard
|>

	Funny, I thought I invented it. :-) 
	I agree with you about co-processes.  I use them to receive messages from 

oracle and spool the output to unix files. To do this you need a sqlplus loop because output is only written when the procedure returns to sqlplus. Since sqlplus doesn't have a loop feature, I have the co-process keep reissuing the same command until a message appears which tells it to stop. The invoked procedure issues a dbms_output.put_line for each message that comes in, then waits one second for the next message and exits if the time expires, or if fifty messages have been written out.

        For those not familiar with co-processes, they are basically programs which run other programs by providing the input and reading the output. They are available in KornShell, but I don't know of other shells that provide them.

        One warning: It can be tricky when you are reading sqlplus output back into your program. If your program is doing a read and sqlplus is doing a read, prompting for input, both will sit there forever, just as if you were sitting at a keyboard and didn't recognize that sqlplus was issuing a prompt.

-- 
-- 
============================================================
Donald A. Smith         No, that was JOHN Smith
eddas_at_huber.com         I have never met Pocahontas       

Received on Fri Mar 15 1996 - 00:00:00 CET