?
Solved

Is there a "Wait" command in Oracle?

Posted on 1999-01-29
5
Medium Priority
?
6,584 Views
Last Modified: 2008-03-10
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
Comment
Question by:parkerea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 2

Accepted Solution

by:
dslavin earned 280 total points
ID: 1083911
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
 
LVL 2

Expert Comment

by:dslavin
ID: 1083912
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
 
LVL 2

Expert Comment

by:dslavin
ID: 1083913
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
 

Author Comment

by:parkerea
ID: 1083914
Thanks -- just the sort of thing I was looking for.
0
 

Author Comment

by:parkerea
ID: 1083915
Oops... I intended to accept your answer, but accidentally added a comment.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question