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: ---------------------------------------- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
