Hi, We do this quite frequently and there are a number of ways of solving it. One way is to approach the problem with some lateral thinking. Try adding an "IN OUT" type parameter to your stored procedures and use a "wrapper procedure" or anonymous PL/SQL block that calls each procedure in sequence passing this IN OUT parameter between each other.
/* this anonymous block will work directly with DBMS SCHEDULER or DBMS JOB. */ /* you can reformat this so that it is a stored procedure to simplify your scheduler call */ declare -- set this local variable to = "Y" or "N" depending on the results of each of your procedures -- initialize it to "Y" so that proc1 will run first continue_proc varchar2(1) := 'Y'; begin procedure_1 ( continue_proc ); procedure_2 ( continue_proc ): procedure_3 ( continue_proc ); end; Within procedure_1, 2 and 3 at the end of the code, set your IN OUT parameter = "Y" if it successfully ends or "N" in your exception block if the procedure fails. Within each procedure, set a conditional statement that reads the "continue_proc" variable which is passed between each procedure sequentially. If at any time the value read is "N" then raise an exception within the proc. for example: create or replace procedure_1 ( proceed_param in out varchar2 ) is begin if proceed_param = 'Y' then ... your procedure code here ... end if; exception when others then proceed_param = 'N'; end procedure_1; *How this works*: PL/SQL is procedural. It runs commands from start to finish. Calling each procedure in order acknowledges the sequential nature of each procedure and each will not start until the previous is completed. I simply added the notion of an IN OUT parameter so that each procedure will "talk" to each other and inform the other if it successfully completed. Otherwise, the whole process exits out gracefully if any previous dependent procedure fails or errors out. Rich Pascual On Tue, Jan 18, 2011 at 7:27 AM, Gopakumar Pandarikkal < pandarik...@gmail.com> wrote: > I have never done anything like this. but this link may help you. > > devx.com/dbzone/10MinuteSolution/20902/1954 > > regards > Gopa > > On Jan 18, 2011 8:31 PM, "Iyad Bacdounes" <iya...@gmail.com> wrote: > > > > Hello Group, > > > I have three procedures > > > Procedure_One > Procedure_Two > Procedure_Three > > > Procedure_Two and Procedure_Three are depending on results from > Procedure_One > > > My question is what is the best way to let both Procedure_Two and > Procedure_Three to work immediately after Procedure_One is finished in > parallel sessions > > and I need to put them in a Job, > > > for now I'm using three jobs and I'm depending on expected time for > finishing Procedure_One > it's not a professional way > > Best Regards, > Iyad > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
