custom scheduling

[hrishy]

Hi

Does anybody have an example to do this

1)need to run three proceedures in a sequence

a)p1
b)p2
c)p3

i am planning to use dbms_jobs for this..the idea is to have a sequence prefixed table and have dbms_jobs read that sequence prefixed table and excute the jobs one after the other...(can dbms_jobs really do it ? )

if job p1 or p2 fails even then job p3 should be executed..

how can i do it ?

regards
Hrishy

[ales]

Hi,
I think it's possible. It's more about PL/SQL programming than about abilities of dbms_job.
Of course you could call dbms_job.submit with all the procedures:

dbms_job.submit(jobno,'p1;p2;p3;' ....)

Using the prefixed table you could:

for proc in (select procname from prefixed_table order by something)
loop
  if proc.procname='P1' then p1;
  elsif proc.procname='P2' then p2;
  elsif proc.procname='P3' then p3;
  end if;
end loop;

Write the code above to stored procedure p0 and:

dbms_job.submit(jobno,'p0;' ....)

The bad thing is that you need change p0 when you want to schedule another procedure.
Regards,

[hrishy]

Hi ales

boy That was really a pleasent surprise...i was just wundering whaether dbms_job can execute the proceedure in a sequence p1 p2 p3 thats really a surprise coz i though the sequence was not guranteed...i will try your thing lemme see what happens..

Thank you very much

regards
Hrishy

[Calvin_Qiu]

As the manual said, the job process just treat it (the WHAT parameter) as a PL/SQL procedure, so it will have the same aspect as a PL/SQL procedure.