Home » SQL & PL/SQL » SQL & PL/SQL » dbms_lock.sleep
dbms_lock.sleep [message #585182] Wed, 22 May 2013 15:04 Go to next message
ora1980
Messages: 247
Registered: May 2008
Senior Member
I need to keep checking for one hour that if job A completes, if yes, then run job B, else wait for 5 minutes and check again, keep doing it for one hour

is below correct approach to keep checking for one hour ?



declare
v_complete varchar2(1);
begin
  
for i in 1..trunc(SYSDATE + (1/24))  loop    
 -- logic to check job complete
 
if v_complete = 'Y' then
  -- run procedure for job b
  exit; -- job A complete, no need to check
 else
   dbms_lock.sleep(300); -- job A is not complete yet, sleep for 5 minutes, and check again
 
end if;
 
end loop;
end;
/ 



Re: dbms_lock.sleep [message #585183 is a reply to message #585182] Wed, 22 May 2013 15:07 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
What would a loop from 1 to a date mean?
Re: dbms_lock.sleep [message #585184 is a reply to message #585183] Wed, 22 May 2013 15:10 Go to previous messageGo to next message
ora1980
Messages: 247
Registered: May 2008
Senior Member
does not mean anything, something is wrong
Re: dbms_lock.sleep [message #585187 is a reply to message #585184] Wed, 22 May 2013 15:38 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
Why not add 1 more line to job A that starts job B as the last thing it does before it terminates?
Re: dbms_lock.sleep [message #585194 is a reply to message #585182] Wed, 22 May 2013 18:38 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I need to keep checking for one hour that if job A completes, if yes, then run job B, else wait for 5 minutes and check again, keep doing it for one hour
Thid sounds like a normal job chain, where the chain branches depending on the outcome of a previous job,
http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse006.htm#CHDEFJGF
Re: dbms_lock.sleep [message #585342 is a reply to message #585194] Thu, 23 May 2013 15:25 Go to previous messageGo to next message
Bill B
Messages: 1086
Registered: December 2004
Senior Member
try DBMS_SCHEDULER
Re: dbms_lock.sleep [message #585351 is a reply to message #585342] Fri, 24 May 2013 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not what John suggested? In addition he gave the link to the documentation!

Regards
Michel
Re: dbms_lock.sleep [message #585375 is a reply to message #585351] Fri, 24 May 2013 07:43 Go to previous message
Bill B
Messages: 1086
Registered: December 2004
Senior Member
I missed it. Sorry Michel
Previous Topic: Replacing multiple characters
Next Topic: How to extract full text from clob where I have a distict select
Goto Forum:
  


Current Time: Sat Aug 30 21:18:23 CDT 2014

Total time taken to generate the page: 0.12297 seconds