0

I have a business-flow consisting of several steps implemented as stored procedures in Oracle. I want wrap the whole flow, which is linear (Step1 -> Step2 -> Step3 ->...) into a job that will run monthly.

The problem is that some of these steps are critical, and when they fail, I want the job to end with status FAILED, and issue an alert to my email. I thought a good approach would be to use RAISE_APPLICATION_ERROR when my validations fail, thus also transmitting a custom error message.

My question is, if I call RAISE_APPLICATION_ERROR in any of the procedures (and not catching it externally), will the job automatically fail, abort (end) and alert? (in the same manner as an uncaught exception causes a program to crash).

Improve this question
4
  • Yes, it will end with an error. You'll need to check the user_scheduler_* views for the status of the job (or the all_ or dba_ versions of those views). – Colin 't Hart Sep 24 '14 at 9:00
  • So the alert must be sent manually? For instance, SQL Server automatically send an alert upon failing a job. – ddaniel Sep 24 '14 at 10:25
  • 1
    Oracle does very few automatic things with your code. You are however free to add features to your code. If you have a step by step procedure, you might be better off using job chain and enable email notification. Features are available, you just have to use them. And if you don't feel like it, job errors are reported in alert.log and if you monitor that, you will get notified too. – Raj Sep 24 '14 at 11:50
  • I found another way to accomplish this. Inside the procedure, when I raise the exception thus interrupting its execution, I can also call a generic send_mail procedure, which I can develop separately using utl_smtp. – ddaniel Sep 24 '14 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.