Jared,
I didn't know this one ...
Thanks
Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
From: Jared Still [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 01, 2002 10:20 AM
To: Jamadagni, Rajendra; '[EMAIL PROTECTED]'
Subject: Re: dbms_lock.sleep irregularities
Raj,
Actually, that isn't the problem. The to_date is unnecessary, but if
you examine my example you will see that the correct number of
seconds is calculated.
The actual problem is described at :
http://www.jlcomp.demon.co.uk/faq/sleep.html
Thanks to Waleed to locating that.
The short version is this, dbms_lock.sleep is inaccurate by 2.4%
up to 2100 seconds, and unusable for any value over 2097 seconds.
Jared
On Friday 01 November 2002 05:01, Jamadagni, Rajendra wrote:
> Jared,
>
> remove the to_date ... that is causing the problem ...
>
> =============== DOESN'T WORK =============================
> oraclei@pallas-ACPT2> sys
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 1 07:51:00 2002
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Connected.
> SQL> set serveroutput on
> SQL> @r2
>
> SYSTIMESTAMP
> ---------------------------------------------------------------------------
> 01-NOV-02 07.51.06.277290 AM -05:00
>
> -28266
>
> PL/SQL procedure successfully completed.
>
>
> SYSTIMESTAMP
> ---------------------------------------------------------------------------
> 01-NOV-02 07.51.06.347844 AM -05:00
>
> SQL> get r2
> 1 select systimestamp from dual
> 2 /
> 3 declare
> 4 seconds integer;
> 5 begin
> 6 -- seconds from now til 08:00 AM
> 7 select (to_date(trunc(sysdate)+(8/24)) - sysdate ) * ( 24*60*60)
> 8 into seconds
> 9 from dual;
> 10 dbms_output.put_line(seconds);
> 11 dbms_lock.sleep(seconds);
> 12 end;
> 13 /
> 14* select systimestamp from dual
> SQL> exit
> ================== END =================================
>
> and
>
> =============== WORKS FINE ===============================
> oraclei@rhea-ACPT1> sys
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 1 07:50:24 2002
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Connected.
> SQL> set serveroutput on
> SQL> @r1
>
> SYSTIMESTAMP
> ---------------------------------------------------------------------------
> 01-NOV-02 07.50.36.239584 AM -05:00
>
> 564
>
> PL/SQL procedure successfully completed.
>
>
> SYSTIMESTAMP
> ---------------------------------------------------------------------------
> 01-NOV-02 08.00.13.854655 AM -05:00
>
> SQL> get r1.sql
> 1 select systimestamp from dual
> 2 /
> 3 declare
> 4 seconds integer;
> 5 begin
> 6 -- seconds from now til 08:00 AM
> 7 select ((trunc(sysdate)+(8/24)) - sysdate ) * ( 24*60*60)
> 8 into seconds
> 9 from dual;
> 10 dbms_output.put_line(seconds);
> 11 dbms_lock.sleep(seconds);
> 12 end;
> 13 /
> 14* select systimestamp from dual
> ================== END =================================
> Raj
> ______________________________________________________
> Rajendra Jamadagni MIS, ESPN Inc.
> Rajendra dot Jamadagni at ESPN dot com
> Any opinion expressed here is personal and doesn't reflect that of ESPN
> Inc.
>
> QOTD: Any clod can have facts, but having an opinion is an art!
----------------------------------------
Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: 7bit
Content-Description:
----------------------------------------
----------------------------------------
Content-Type: text/plain; charset="iso-8859-1"; name="ESPN_Disclaimer.txt"
Content-Transfer-Encoding: 7bit
Content-Description:
----------------------------------------
*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1
