Home » SQL & PL/SQL » SQL & PL/SQL » Parallel execution of procedures
Parallel execution of procedures [message #187350] Sat, 12 August 2006 09:06 Go to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Hello All,
I have a procedure(p_start_MRP_process) which takes, a long time to execute.
It takes start and end dates as parameters.
I need to call this procedure from a UNIX script, starting five instances of the procedure call, at approx the same time, each with different date parameters.

So I need to have
p_start_MRP_process('01-JAN-2006','31-JAN-2006')
p_start_MRP_process('01-FEB-2006','31-FEB-2006')
p_start_MRP_process('01-MAR-2006','31-MAR-2006')
p_start_MRP_process('01-APR-2006','31-APR-2006')
p_start_MRP_process('01-MAY-2006','31-MAY-2006')
all running at the same time.

How can I acheive this?

Thanks
Re: Parallel execution of procedures [message #187351 is a reply to message #187350] Sat, 12 August 2006 09:51 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
write five different scripts & invoke them separately.
Re: Parallel execution of procedures [message #187353 is a reply to message #187351] Sat, 12 August 2006 10:35 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, you could schedule those procedures and make them run whenever you like.
Re: Parallel execution of procedures [message #187356 is a reply to message #187350] Sat, 12 August 2006 10:47 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
I need to run this from a single UNIX script.
That is.
the script should loop 5 times,
each time it should start the call to procedure, and comeout without having to wait for the procedure to complete. How do I acheive the "having to comeout without waiting for the procedure to complete" from a UNIX script?

Here is the pseudo code what I want to acheive:

UNIX SCRIPT
==============

BEGIN
   LOOP ------------5 times

      p_Start_date:={Start Date Logic};
      p_End_date  :={End Date Logic};
      -- Call procedure and exit without waiting to complete
      sqlplus p_start_MRP_process(p_Start_date,p_End_date);
      

   END LOOP
END


Thanks

[Updated on: Sat, 12 August 2006 10:54]

Report message to a moderator

Re: Parallel execution of procedures [message #187358 is a reply to message #187350] Sat, 12 August 2006 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>UNIX SCRIPT
No it is not a UNIX SCRIPT. It is PL/SQL code.
With real Unix scripts they could be run in parallel in the background.
Re: Parallel execution of procedures [message #187365 is a reply to message #187350] Sat, 12 August 2006 17:13 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
You could submit them via DBMS_SCHEDULER or DBMS_JOB, assuming you have five free job queues available.

btw,
pkirangi wrote on Sat, 12 August 2006 09:06

I need to have
p_start_MRP_process('01-JAN-2006','31-JAN-2006')
p_start_MRP_process('01-FEB-2006','31-FEB-2006')
p_start_MRP_process('01-MAR-2006','31-MAR-2006')
p_start_MRP_process('01-APR-2006','31-APR-2006')
p_start_MRP_process('01-MAY-2006','31-MAY-2006')

Does that procedure take two character strings as parameters, or two dates?

If they are supposed to be dates, there is something odd about the February and April ones Wink

[Updated on: Sat, 12 August 2006 17:14]

Report message to a moderator

Re: Parallel execution of procedures [message #187371 is a reply to message #187350] Sat, 12 August 2006 21:54 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Thanks William.
Yes the parameters are DATE type. Would you know of an example code which utilizes, either dbms_scheduler or dbms_job?

Also Anacedent,
How would I run a UNIX script in the background?
What I could do is write a UNIX script "A" which calls the procedure.
Then write another Master UNIX script which calls script "A" in the background 5 times, with different parameters.

Thanks

[Updated on: Sat, 12 August 2006 22:06]

Report message to a moderator

Re: Parallel execution of procedures [message #187417 is a reply to message #187371] Sun, 13 August 2006 17:26 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
If the parameters are dates, you should pass dates to them, not character strings.

Also if they are meant to be dates, the February and April values are wrong.

A good description of DBMS_SCHEDULER is here.
Re: Parallel execution of procedures [message #187427 is a reply to message #187417] Mon, 14 August 2006 00:07 Go to previous message
pkirangi
Messages: 74
Registered: August 2005
Member
Thanks Williams.
Previous Topic: etl approach
Next Topic: inserting
Goto Forum:
  


Current Time: Thu Dec 08 19:55:05 CST 2016

Total time taken to generate the page: 0.12013 seconds