Home » SQL & PL/SQL » SQL & PL/SQL » Avoid Parallel Execution of a Procedure and DBMS_JOB (merged)
Avoid Parallel Execution of a Procedure and DBMS_JOB (merged) [message #387867] Sat, 21 February 2009 01:47 Go to next message
palanisvr
Messages: 21
Registered: March 2007
Junior Member
HI ,
I have a job named 'proc1;' which is scheduled for one hour and it should run after one hour only if the existing scheduled job
i.e 'proc1;' is completed , there is no constraint that it should run only after one hour if the existing job takes more than a hour there are no issues only thing is that it should only run after the existing one is completed .


It should be something like this :

if ( proc1 has completed the execution )

then

run proc1;

end if



Please help in this regard .

Thanks in Advance

Re: DBMS_JOB [message #387871 is a reply to message #387867] Sat, 21 February 2009 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use a signal, for instance a row in a table that is locked during proc1 execution or dbms_alert or dbms_lock.
Or you can schedule the job from proc1 itself.

Regards
Michel

[Updated on: Sat, 21 February 2009 02:47]

Report message to a moderator

Re: DBMS_JOB [message #387874 is a reply to message #387871] Sat, 21 February 2009 03:31 Go to previous messageGo to next message
palanisvr
Messages: 21
Registered: March 2007
Junior Member


Actual Scenario

user will pass two parameters to the proc1 (DAILY , 3 ) which in turn should run proc2 for a schedule
date which satisfies the below conditons

proc1(period =>DAILY , p_diff_days ==> 3 )

so the proc2 should be scheduled in such a way baring below conditions:

Actually we have a Table which contains start_date and end_date

the job should run scheduled date should be calculated as

schedule_date:=start_date - p_diff_days ;

==============
Condition : 1
==============

if the scheduled day falls less than sysdate then it should run on sysdate itself


for example :

sysdate = 21/feb/2009

start_date=22/feb/2009

then it scheduled_date= (start_date - 3) ==> 19/feb/2009

which falls less than sysdate then it should run on sysdate itself


so subsequently it should do the same for the following dates :

sysdate = 21/feb/2009

if the start_Date = 23/feb/2009 then
(start_date - 3) ==> 20/feb/2009 which falls less than sysdate then it should run on sysdate itself
similarly if it the schedule date falls on sysdate then it should run of sysdate itself .


so the proc2 should not run parallely , it should only run when the proc2 is completed


===============
Condition : 2
===============

if the scheduled day falls greater than sysdate then it should run on appropiate time

for example : start_date=25/feb/2009

then it should be scheduled on start_date - 3 = 22/feb/2009


SO the proc2 should be scheduled in such a way that for all start_dates it
should run as per the user specifications that is before the start_date
schedule_date:=start_date - p_diff_days ;


Please note than This is a one time execution process .


I need it urgently
Please help me in this .....

Thanks in advance





Re: DBMS_JOB [message #387888 is a reply to message #387867] Sat, 21 February 2009 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you
Re: DBMS_JOB [message #387895 is a reply to message #387874] Sat, 21 February 2009 09:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
proc1 (<my functional parameters>, p_next_date in out date)
begin
  <do what I want>
  p_next_date := <the next execution date I want>
end;

and use dbms_job to schedule it.

Regards
Michel

[Updated on: Sat, 21 February 2009 12:35]

Report message to a moderator

Re: DBMS_JOB [message #387918 is a reply to message #387895] Sat, 21 February 2009 12:10 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel Cadot wrote on almost every day, any time
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel


./fa/5767/0/
Re: DBMS_JOB [message #387919 is a reply to message #387918] Sat, 21 February 2009 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, I first formatted it but as it is pseudo-code and not real one, I thought formatted it is misleading and will lead to false conclusion it is real code.
For instance "<the next execution date I want>" can be (and will be given OP's requirements) a piece of code and not just a simple assignment.

I add it if you want... Smile

Regards
Michel

[Updated on: Sat, 21 February 2009 12:37]

Report message to a moderator

Re: DBMS_JOB [message #387921 is a reply to message #387919] Sat, 21 February 2009 12:52 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, no!

The code (pseudo code, actually) was perfectly readable in its unformatted state. My point was: you don't have to insist on formatting absolutely each and every time.

And, the most important - quoting you because of you was ultimate fun this Saturday's afternoon.
Re: DBMS_JOB [message #387922 is a reply to message #387921] Sat, 21 February 2009 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing
Re: DBMS_JOB [message #387928 is a reply to message #387867] Sat, 21 February 2009 21:06 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
From: http://www.orafaq.com/forum/t/88153/0/
"Avoid the following keywords: urgent/please/help/now/immediately (and derivatives)"

>I need it urgently
Then you need to pay an expert for the quick desired answer.
Re: DBMS_JOB [message #387929 is a reply to message #387928] Sat, 21 February 2009 22:05 Go to previous messageGo to next message
palanisvr
Messages: 21
Registered: March 2007
Junior Member
i am sorry for that this is the first time i am posting a question in the forum .
Re: DBMS_JOB [message #387941 is a reply to message #387929] Sun, 22 February 2009 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And feedback on the solution we provide you is most welcome.

Regards
Michel

[Edit: typo]

[Updated on: Sun, 22 February 2009 12:43]

Report message to a moderator

Re: DBMS_JOB [message #387969 is a reply to message #387867] Sun, 22 February 2009 12:56 Go to previous messageGo to next message
palanisvr
Messages: 21
Registered: March 2007
Junior Member
i had found a solution for it

i had scheduled the procedure one after another to execute the same

declare
jobno number(2);
begin
dbms_job.submit(
jobno,
'begin proc1; proc1; end;',
sysdate,
null
);
commit;
end;



Re: DBMS_JOB [message #387970 is a reply to message #387867] Sun, 22 February 2009 13:11 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
I failed to understand how
DECLARE 
  jobno  NUMBER(2); 
BEGIN 
  dbms_job.Submit(jobno,'begin proc1; proc1; end;',SYSDATE, NULL); 
  COMMIT; 
END;


meets the original requirements below
========================================
user will pass two parameters to the proc1 (DAILY , 3 ) which in turn should run proc2 for a schedule
date which satisfies the below conditons
proc1(period =>DAILY , p_diff_days ==> 3 )
so the proc2 should be scheduled in such a way baring below conditions:
Actually we have a Table which contains start_date and end_date
the job should run scheduled date should be calculated as
schedule_date:=start_date - p_diff_days ;

Why is last posted code acceptable?

[Updated on: Sun, 22 February 2009 13:13]

Report message to a moderator

Avoid Parallel Execution of a Procedure [message #388258 is a reply to message #387867] Tue, 24 February 2009 01:07 Go to previous messageGo to next message
palanisvr
Messages: 21
Registered: March 2007
Junior Member
HI All,

I have a procedure p1
i have to restrict p1 from execting parallely
Would anyone can help me in achieving this

Thanks In Advance
Re: Avoid Parallel Execution of a Procedure [message #388260 is a reply to message #388258] Tue, 24 February 2009 01:11 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.
Re: Avoid Parallel Execution of a Procedure [message #388271 is a reply to message #388258] Tue, 24 February 2009 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL does not execute parallely.

Regards
Michel
Re: Avoid Parallel Execution of a Procedure [message #388275 is a reply to message #388258] Tue, 24 February 2009 01:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You could do this using some token that needs to be "free" in order for the procedure to be allowed to run.
Problem with these methods is always that it is possible that some process crashes, which results in failure to return the token to its "free" position.
Re: Avoid Parallel Execution of a Procedure [message #388279 is a reply to message #388271] Tue, 24 February 2009 02:11 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Michel Cadot wrote on Tue, 24 February 2009 08:42
PL/SQL does not execute parallely.

True for executing the procedure in one session.
However, it may be run concurrently from different sessions. If this is the problem (= it is necessary to assure that the procedure is running at most once at a time), using DBMS_LOCK can achieve it.
Re: Avoid Parallel Execution of a Procedure [message #388284 is a reply to message #388279] Tue, 24 February 2009 02:20 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, I think you are right and this is the same question as his previous topic http://www.orafaq.com/forum/m/387867/102589/#msg_387867 and we already answered.

I merge the 2 although there already are several questions in the previous topic.

Regards
Michel

[Updated on: Tue, 24 February 2009 02:21]

Report message to a moderator

Previous Topic: ORA-01422 / ORA-0651 but why? Please help! (merged 4)
Next Topic: Need help in converting LOB to timestamp
Goto Forum:
  


Current Time: Mon Dec 05 21:29:16 CST 2016

Total time taken to generate the page: 0.21320 seconds