| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: No PL/SQL Timer Function
This is a multi-part message in MIME format.
--------------9200C5421F766F15B7A014DB Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit
Erm ... E-mail address change wreaking a little havoc ... Here's the post w/out the lovely add-ons.
Steve,
I'll add another possible twist. We use dbms_pipe to create persistent daemons
that can be told when to exit, such as at database shutdown or any other necessary point. Code sample is:
dbms_application_info.set_action('Sleeping... Loop '||to_char(Counter)||'
of '||to_char(Iterations));
/* Listen on the pipe for a 'terminate' message */
PIPEstatus := dbms_pipe.receive_message('SLEEP'||v_session.id, 15);
IF PIPEstatus = 0 THEN /* Message receipt successful */
/* Get the message */
dbms_pipe.unpack_message(PIPEmessage);
/* If message received = 'terminate', exit procedure */
IF PIPEmessage = 'terminate' THEN
dbms_application_info.set_module(null, null);
Another_Outer := FALSE;
END IF;
END IF;
Daemons that run like this are controlled by a ksh script that has it's own loop timer, enabling it to intelligently reconnect. Works pretty well.
Cheers,
Casey ...
Steve Orr wrote:
> Thanks to all on the DBMS_LOCK.SLEEP tip. I believe Walt gets to go to the
> hot seat for answering most quickly... Regardless, all were quicker than the
> 1/2 hour I spent looking in the docs ...and that's my "final answer."
>
> Steve Orr
>
> -----Original Message-----
> Sent: Tuesday, July 18, 2000 9:24 AM
> To: 'steve_at_arzoo.com'; Oracle-L; Oracledba
>
> Steve,
>
> Will DBMS_LOCK.SLEEP work for you?
>
> --Walt Weaver
> Bozeman, Montana, USA
>
> -----Original Message-----
> Sent: Tuesday, July 18, 2000 10:01 AM
> To: Oracle-L; Oracledba
>
> I can't find a timer function in PL/SQL. Without a major development effort
> I'd like to QUICKLY write a monitoring script which executes a query at a
> given interval over a certain period of time. Here's a hypothetical
> example... a query against the v$session and v$access tables every 15
> seconds for the next 60 minutes. I need something like a UNIX shell "sleep
> 15" but I want the timer looping to be from a persistent connection so
> there's no connect/disconnect from the database every few seconds. I guess I
> could get a modulus of the results from DBMS_UTILITY.GET_TIME to develop my
> own timer function. Has anyone done something like this in PL/SQL? Any
> ideas? Please? Pretty please?
>
> TIA,
> Steve Orr
>
> --------
> If you're bored, then visit the list's website: http://www.lazydba.com
> (updated daily)
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk
> to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
>
> --
> Author: Steve Orr
> INET: sorr_at_arzoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--------------9200C5421F766F15B7A014DB
Content-Type: text/x-vcard; charset=us-ascii;
name="cdyke.vcf"
Content-Transfer-Encoding: 7bit Content-Description: Card for Casey Dyke Content-Disposition: attachment;
begin:vcard
n:Dyke;Casey
tel;pager:(022) 9436 9290 tel;home:61 2 9948 1487 tel;work:61 2 9005 1073
url:www.realsurf.com org:Excite_at_Home Australia;IT-Ops adr:;;100 Harris Street;Pyrmont;NSW;2042;Australiaversion:2.1
 |
 |