• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7681
  • Last Modified:

Is there a "Wait" command in Oracle?

I want to submit a SQL query to an Oracle database which I know will not come back for a while (preferably without bad style like running a really inefficient join). I know how to do this in Sybase, but having searched the Oracle website, I can not find such a command. In Sybase, the command is:
   waitfor delay "0:01:00"
to cause a Sybase TSQL call to pause for 1 minute, then continue execution. Obviously, this is a Sybase enhancement to ANSI SQL. Is there any similar command in Oracle SQL? In a worst case, does Oracle SQL have control-of-flow logic which would allow it to sit in a loop until a particular time?

Thanks in advance.
0
parkerea
Asked:
parkerea
  • 3
  • 2
1 Solution
 
dslavinCommented:
Here is a stored procedure that will provide a wait of a specified number of seconds:

-- Package to hold 'sleep' procedure and other utilities
create or replace package user_util
IS
   PROCEDURE sleep (num_of_sec IN NUMBER);
END user_util;
/

create or replace package body user_util
IS
  -- Procedure to cause Oracle to sleep NUM_OF_SEC seconds.  Can be invoked before
  -- any other call to provide a wait.
  PROCEDURE sleep (num_of_sec IN NUMBER)
  IS
     start_time NUMBER;
     curr_time NUMBER;
     end_time NUMBER;
  BEGIN
     -- Get current time (SSSSS format returns seconds since midnight)
     -- Thus this routine will not work if started just before midnight
     -- but it could be enhanced to support that as well.
     select to_number(to_char(sysdate,'SSSSS')) into start_time from dual;

     -- Calculate end of sleep time
     end_time := start_time + num_of_sec;

     -- Loop until End Time is reached
     WHILE (1=1) LOOP
       -- Get current time
       select to_number(to_char(sysdate,'SSSSS')) into curr_time from dual;

       -- Compare to end time and exit if time is past
       IF (curr_time >= end_time) THEN
          EXIT;
       END IF;
     END LOOP;
  END sleep;
END user_util;
/

Hope this helps.

0
 
dslavinCommented:
The above procedure can be invoked like this:

   user_util.sleep (10);

   Sleeps for 10 seconds.

P.S.:  You cannot easily specify a wait of less than a second.  It can be done but requires a lot more work.  There was a PAQ regarding this which you can look into for more details.
0
 
dslavinCommented:
Also, If you don't like polling in a loop, then you can:

1) Open an Oracle Pipe
2) Try to receive a message (that noone will send).  One of the parameters to specify on the pipe message receive command is the number of seconds to wait for a message.  So you can set this to how many seconds you want to wait.  Then when the command finishes, your wait is up.  It would be something like this:

PROCEDURE sleep (SLEEP_TIME IN NUMBER)
IS
   status INTEGER;

BEGIN
   -- Create pipe and purge any existing messages
   status := DBMS_PIPE.CREATE_PIPE ('sleep_pipe', 8192, TRUE);
   DBMS_PIPE.RESET_BUFFER;
   DBMS_PIPE.PURGE ('sleep_pipe');

   -- Wait to receive message that will never come
   status := DBMS_PIPE.RECEIVE_MESSAGE ('sleep_pipe', SLEEP_TIME);

   -- Delete pipe
   status := DBMS_PIPE.REMOVE_PIPE ('sleep_pipe');

END;

You can add error checking using the 'status' returned values to make this more solid.
   
0
 
parkereaAuthor Commented:
Thanks -- just the sort of thing I was looking for.
0
 
parkereaAuthor Commented:
Oops... I intended to accept your answer, but accidentally added a comment.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now