Saturday, July 15, 2006

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.

5 comments:

Nicolas Gasparotto said...

Good shot William ;-)

Nicolas Gasparotto

405764 said...

Nothing personal against the original poster of the thread in question, but his threads almost always elicit 50 responses.

Brings to mind his thread about creating gapless sequences.

That thread is pure comedy in its finest.

APC said...

Of course the real WTF (to coin a phrase) is that this guy's DBA would rather he knacker the system with a CPU poll rather than grant him execute on DBMS_LOCK.SLEEP.

Cheers, APC

Phantom Nitpicker said...

Nice try APC, but you didn't really coin that phrase.

APC said...

I thought the accepted use of "to coin a phrase" is as an acknowledgement that one is using a hackneyed phrase for comic effect. Whilst searching for proof I found this thread: http://www.phrases.org.uk/bulletin_board/41/messages/453.html

The final post would appear to take Ol' Nitpicker's line: I really don't understand why those "wags" would find humor in this contradiction. Am I missing something here? I personally would not find any humor in speaking incorrectly but many have said that I tend to be curmudgeon-like at times.

I presume this isn't actually PN, as he would surely use the more elegant "curmudgeonly" (not to mention the English - and hence correct - spelling of "humour").

Cheers, APC