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.
Who is Participating?
dslavinConnect With a Mentor Commented:
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
   PROCEDURE sleep (num_of_sec IN NUMBER);
END user_util;

create or replace package body user_util
  -- 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)
     start_time NUMBER;
     curr_time NUMBER;
     end_time NUMBER;
     -- 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
       END IF;
     END LOOP;
  END sleep;
END user_util;

Hope this helps.

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.
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:

   status INTEGER;

   -- Create pipe and purge any existing messages
   status := DBMS_PIPE.CREATE_PIPE ('sleep_pipe', 8192, TRUE);
   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');


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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.