SLEEP Procs

Can't find the original thread but I read Jonathan Lewis' page on SLEEP http://www.jlcomp.demon.co.uk/faq/sleep.html and it refers to versions 7.3 through 9.2.0.3.

Thus I decided to see where things stand with 10gR2 and found the following which I thought I'd share with the group.

set linesize 121
col package_name format a20
col argname format a9
col data_type format a14
col length format 999
col prec format 999
col pls_type format a14

SELECT package_name, argument_name ARGNAME, data_type, data_length LENGTH, data_precision PREC, pls_type FROM all_Arguments
WHERE object_name = 'SLEEP'
ORDER BY 1; PACKAGE_NAME ARGNAME DATA_TYPE LENGTH PREC PLS_TYPE

-------------------- --------- -------------- ------ ---- --------------
DBMS_BACKUP_RESTORE  SECS      BINARY_INTEGER             BINARY_INTEGER
DBMS_DRS             SECONDS   NUMBER             22   38 INTEGER
DBMS_LOCK            SECONDS   NUMBER             22      NUMBER
USER_LOCK            TENS_OF_M NUMBER             22      NUMBER
                      ILLISECS

SQL> DECLARE
   2 stime TIMESTAMP(9);
   3 etime TIMESTAMP(9);
   4 BEGIN
   5 stime := SYSTIMESTAMP;
   6 dbms_backup_restore.sleep(0.5);
   7 etime := SYSTIMESTAMP;
   8 dbms_output.put_line(etime-stime);    9
  10 stime := SYSTIMESTAMP;
  11 dbms_drs.sleep(0.5);
  12 etime := SYSTIMESTAMP;
  13 dbms_output.put_line(etime-stime);   14
  15 stime := SYSTIMESTAMP;
  16 dbms_lock.sleep(0.5);
  17 etime := SYSTIMESTAMP;
  18 dbms_output.put_line(etime-stime);   19
  20 stime := SYSTIMESTAMP;
  21 user_lock.sleep(50);
  22 etime := SYSTIMESTAMP;
  23 dbms_output.put_line(etime-stime);   24 END;
  25 /
+000000000 00:00:01.000000000
+000000000 00:00:00.500000000
+000000000 00:00:00.500000000
+000000000 00:00:00.500000000

PL/SQL procedure successfully completed.

SQL> It seems the only one that can't handle a fraction is dbms_backup_restore.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org