CODEWITHSUNDEEP
oracle11g
Rbt
Rbt

Reputation: 51

Time delay in Oracle 11g

I have a stored Procedure and I need to put a time delay before that last process is run. the last process is dependent upon the record count in one of the table populated in the beginning. So there is my scenario:

INSERT INTO Table A
SELECT column1,column2, etc FROM Table B WHERE...

Some other sql statements here

Some looping statement here using the record count in Table B

--- Before the last process is run, I need to create a time delay
    here and the time delay depends upon the record count in table B
    Because of the loop. Small record count may set the delay shorter
    while huge record count will set the delay longer -----

HERE IS THE START OF THE LAST PROCESS.

I'm using Oracle 11g, I found the following script from the web. It works but the delay value is not dynamically related to the record count in Table B

   -- Declaring variables
   IN_TIME INT; --num seconds
   v_now DATE;

   -- 1) Get the date & time 
   SELECT SYSDATE INTO v_now FROM DUAL;

   -- 2) Loop until the original timestamp plus the amount of seconds = 
         current date
   LOOP
    EXIT WHEN v_now + (IN_TIME * (1/86400)) = SYSDATE;
   END LOOP;
   -- End of time delay

Does anyone ever done something like this? please shere the script if you don't mind. Thank you!

Upvotes: 0

Views: 6362

Answers (1)

Boneist
Boneist

Reputation: 23588

Sounds like you're after something like:

declare
  v_count number;
begin
  select count(*)
  into   v_count
  from   table_b;

  dbms_lock.sleep(v_count); -- assuming 1 record means 1 second delay required
end;
/

Obviously, if the delay is different (eg. 10 records per second) then you'll have to amend the number of seconds passed into the dbms_loc.sleep() procedure.

Upvotes: 2

Related Questions