1

I'd like to find the best way to handle exceptions (failure of any steps) from an Oracle scheduler job chain (11gR2).

Say I have a chain that contains 20 steps. If at any point the chain exits with FAILURE, I'd like to do a set of actions. These actions are specific to that chain, not the individual steps (each step's procedure may be used outside of scheduler or in other chains).

Thanks to 11gR2, I can now setup an email notification on FAILURE of chain, but this is only 1 of several actions I need to do, so its only a partial solution for me.

The only thing I can think of is have another polling job check the status of my chain every x minutes and launch the failure actions when it sees the latest job of the chain exited with FAILURE status. But this is a hack at best imo.

What is the best way to handle exceptions for a given job chain?

thanks

Ollie
  • 17,058
  • 7
  • 48
  • 59
tbone
  • 15,107
  • 3
  • 33
  • 40

1 Answers1

1

The most flexible way to handle jobs exceptions in general is to use a job exception monitoring procedure and define the jobs to generate events upon job status changes. The job exception monitoring procedure should watch the scheduler event queue in a loop and react upon events in a way you define.

Doing so takes away the burden to have to create failure steps for about each and every job step in a chain. This is a very powerful mechanism.

by lack of time: in the book is a complete scenario of event based scheduling. Will dig one up later.

  • thanks. So I'd use Oracle AQ and raise a failure event from my chain, then setup a separate event based job using that queue? Not much experience with AQ, can u give a simple example? thanks again – tbone Dec 15 '11 at 12:06
  • Hi, see comments; have to run now, later! –  Dec 15 '11 at 16:10
  • Lucky for me, the one free chapter they give for this book is on events! I won't copy/paste example from the book tho, and will probably still buy the ebook, looks good. Thanks @ik_zelf , if you have a simple example for everyone, it would still be much appreciated. – tbone Dec 16 '11 at 16:30
  • some guys have all the luck ;-) Glad that you could use it and I saw the article I thought about is missing from my blog. I will soon add it there. –  Dec 16 '11 at 20:15