Re: dbms_lock.sleep irregularities

From: <jkstill_at_cybcon.com>
Date: Fri, 01 Nov 2002 10:18:49 -0800
Message-ID: <F001.004FA0A7.20021101101849@fatcity.com>

Here is a working version of accusleep if anyone is interested. I ran a test to sleep for about 65 minutes, and the result was within 1 second of the target time.

08:56:25 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> @accusleep

Procedure created.

08:56:28 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> @accutest 3809

PL/SQL procedure successfully completed.

09:59:59 rsysdevdb.radisys.com - jkstill_at_dv01 SQL>

Here is the procedure:

---

create or replace procedure accusleep ( seconds_in number ) is

   v_chunk_size constant integer := 100;
   v_compensation constant number := 0.976;
   v_chunks integer;
   v_remainder integer;
   v_seconds integer;

begin

   v_seconds := seconds_in * v_compensation;
   v_chunks := trunc(v_seconds/v_chunk_size);
   v_remainder := mod(v_seconds, v_chunk_size);

   for i in 1..v_chunks
   loop

      dbms_lock.sleep(v_chunk_size);
   end loop;
   dbms_lock.sleep(v_remainder);

   --dbms_output.put_line(v_chunks);
   --dbms_output.put_line(v_remainder);

end;
/

---

Here is the test:

declare

   seconds integer;
begin

• seconds from now til 10:00 AM select (to_date(trunc(sysdate)+(10/24)) - sysdate ) * ( 24*60*60) into seconds from dual; dbms_output.put_line(seconds); accusleep(seconds); end;
  /
  

Jared

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Fri Nov 01 2002 - 12:18:49 CST