Pause for thought

Requirement:

Can I let a procedure wait for a specific time (10 seconds) before inserting values into a table?

One solution (according to a poster on OTN - don't try this at home):

declare
  dStart DATE := SYSDATE;
  nDiff  NUMBER;
begin
  dbms_output.put_line('dStart: '||TO_CHAR(dStart, 'dd.mm.yyyy hh24:mi:ss'));
  LOOP
    nDiff := (SYSDATE - dStart)*86400;
    EXIT WHEN nDiff >= 10;
  END LOOP;
  dbms_output.put_line('nDiff: '||nDiff);
  dbms_output.put_line('END: '||TO_CHAR(SYSDATE, 'dd.mm.yyyy hh24:mi:ss'));
end;
/

Or if you prefer,

create or replace function timeout
    return number
is
begin
    return to_number(to_char(sysdate,'SSSSS'));
end timeout;
/

Function created.

create or replace procedure tcal( t in number ) as
    a  number:=1;
    st number:=to_number(to_char(sysdate,'SSSSS'));
    x varchar2(12);
    y varchar2(12);
begin
    while(a<99999999) loop
        dbms_output.enable(100000); ---> Just to make loop busy...
        a := a + 1;
        exit when (timeout - st)>=t;
    end loop;

    x:=to_char(trunc(sysdate)+st/(24*60*60),'HH:MI:SS AM');

    dbms_output.put_line('       Started: '||x);

    y:=to_char(trunc(sysdate)+timeout/(24*60*60),'HH:MI:SS AM');

    dbms_output.put_line('       Ended:   '||y);
    dbms_output.put_line(timeout-st||' seconds reached...');
end ;
/

Procedure created.

Now that is all well and good, you may say, after all the cpu isn't doing anything else at the moment and these days they rarely catch fire, but couldn't we just use the supplied procedure DBMS_LOCK.SLEEP? Well apparently it is impractical. Those with a quiet afternoon to spare might like to follow the reasons why, at forums.oracle.com/forums/thread.jspa?threadID=402345.