| 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.
--------------49326B8AA4ADDC8772F8EDD0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit
iber to the list, you must join the list first before you can post. To get help with subscribing to a list, send an E-mail message to:
ListGuru_at_fatcity.com
and in the message BODY, put:
HELP
LISTS
Sincerely,
ListGuru Mailer Daemon
This is a multi-part message in MIME format.
--------------FD09156833E05BC0ACE0365B Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit
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:
> 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
>
> --
> 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).
--------------49326B8AA4ADDC8772F8EDD0
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
 |
 |