Oracle-L: RE: dbms_lock.sleep irregularities

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Thu, 31 Oct 2002 15:04:58 -0800
Message-ID: <F001.004F9388.20021031150458@fatcity.com>

Hi Jared,

You do not need the "to_date" function, just:

select (trunc(sysdate)+(10/24) - sysdate ) * ( 24*60*60) into seconds from dual;

Regards,

Waleed

-----Original Message-----
Sent: Thursday, October 31, 2002 5:35 PM To: Multiple recipients of list ORACLE-L

Dear List,

I wrote the following bit of code yesterday to cause a process to kick off at a particular time. This was not doable via DBMS_JOB, as the code that follows this bit is a sqlplus script from an app vendor.

When I ran this last night, I expected it to sleep til 20:00.

Instead, it slept for about 45 minutes.

Here's an example from this morning that should have slept until 10:00 AM, but instead only slept 25 minutes.

Any ideas? Did I code something wrong here?

This is 8.1.6.3 on Windows NT 4 SP 6.

Just change the '10' below to some hour > sysdate

Thanks,

Jared

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); dbms_lock.sleep(seconds); end;

09:10:15 RSYS13 - agile_at_orcl SQL> /
2681

PL/SQL procedure successfully completed.

09:36:05 RSYS13 - agile_at_orcl SQL> select 2861/3600 from dual;

2861/3600

.794722222

1 row selected.

14:13:02 RSYS13 - agile_at_orcl SQL>

--

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

Author:
INET: Jared.Still_at_radisys.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).
--

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

Author: Khedr, Waleed
INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------