Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: No PL/SQL Timer Function

Re: No PL/SQL Timer Function

From: Casey Dyke <>
Date: Wed, 19 Jul 2000 11:12:04 +1000
Message-Id: <>

This is a multi-part message in MIME format.

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.


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'||, 15);
      IF PIPEstatus = 0 THEN      /* Message receipt successful */

        /* Get the message */


        /* 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.


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: ''; 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:
> (updated daily)
> to unsubscribe, send a blank email to
> to subscribe send a blank email to
> --
> Author: Steve Orr
> 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: (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).

Content-Type: text/x-vcard; charset=us-ascii;  name="cdyke.vcf"

Content-Transfer-Encoding: 7bit
Content-Description: Card for Casey Dyke
Content-Disposition: attachment;



tel;pager:(022) 9436 9290
tel;home:61 2 9948 1487
tel;work:61 2 9005 1073

org:Excite_at_Home Australia;IT-Ops
adr:;;100 Harris Street;Pyrmont;NSW;2042;Australia
email; Received on Tue Jul 18 2000 - 20:12:04 CDT

Original text of this message