Exception Activity not returning user defined exception hand

[evee1]

I have the following (simplified) sequence design:

Code: Select all

ServerJob ---> ParallelJob(LogSuccess)       ExceptionActivity ---> ParallelJob(LogError) ---> Terminator

The Automatically Handle Activities that Fail option is set for this sequence.
The server job calls the Stored Procedure using STP Stage. The SP has an exception handler defined as RAISE_APPLICATION_ERROR (-20002, 'Invalid job code ''' || job_code || '''').

I would like to be able to log this error message ('Invalid job code <code>') in the Parallel Job following the exception activity.

When the Server Job aborts the exception is called as expected, but although I can see the correct error being raised in the Server Job log:

Test_CREATE_JOB_INSTANCE.abc_START.Test_CREATE_JOB_INSTANCE: ORA-20002: Invalid job code 'abc' ORA-06512: at "DM.TEST", line 62 ORA-06512: at line 1

the Exception Activity only provides me with the following values:
exc_Exception.$ErrSource = "job_PMDCreateProcessInstance"
exc_Exception.$ErrNumber = 1
exc_Exception.$ErrMessage = "Unhandled abort encountered in job Test_CREATE_JOB_INSTANCE.abc_START"

Is there anyway to make the Exception Activity return the actual error number and message that has been raised by the aborted job?

[ray.wurlod]

No, because it's out of scope by the time the Exception Handler triggers.

You could re-vamp the server job to post the error code in its user status area (perhaps in an after-job subroutine). Or you could create a Routine activity to scan the server job's log.

[evee1]

Thanks for the suggestions.

I have created a Before/After routine that extracts the ORA error from the latest run of the job. It seems to be extracting the error correctly.

But now I'm not sure how to call it to get to the information that I need.

If I call this routine as an after-job subroutine (and add DSSetUserStatus to it), I can't really query the user status after the job aborts, since on exception I land in the exception stream and I do not have access to the User Status any more.

I thought of extracting the data from the job log by calling this routine (converted to transform type) from within the excpetion stream, but here I don't have enough info about the job that aborted and caused an exception in the first place.
The sequence I have included in my first post was a simpified example. My actual sequence can have more jobs that potentially abort and I need to be able to identify where the source of the fail is before I query the log.
The exc_Exception.$ErrSource gives me the name of the activity within the sequence, but I don't know (maybe there is a way to do it) how to get the actual job name so that I could retrieve the log for it.

So I'm still searching for the solution.

[evee1]

The only other option I can think of is calling my routine as after-job and dumping the error message into the file and then reading it from the file when exception occurs. I would like to avoid use files though if there is another way.

[ray.wurlod]

You could have an explicit Failure trigger on the server job's Job activity, leading (through a Sequencer) to the same stream of processing that is executed following the Exception Handler (or a modified stream).

[evee1]

Yes, passing user status via the direct trigger link worked. Thanks! It make the sequence design more "busy" though , but I might have to live with that.

I have a more general question then. What is an example of the flow when using exception activity is the best option? In a case of my sequence it wasn't, as I could not get the relevant required information for the error reporting.

[ray.wurlod]

I did give you an alternative where you could have used the Exception Handler absolutely (a downstream Routine).

[evee1]

Sorry, but I don't quite follow. Do you mean having "a Routine activity to scan the server job's log." in the Exception Handler?

But how do I know which log to scan? The error information is in the log of the job that failed. I suppose I would have first to scan the sequence log to get the name of the job that actually failed, and then scan this job's log.

[ray.wurlod]

Well, I had understood that it was only the server job that was of interest. Even so, the $ErrSource activity variable will give you the name of the activity that triggered the Exception Handler, and the routine could use that information to determine which job's log to interrogate.

[evee1]

Unfortunately $ErrSource gives me the name of the activity within the sequencem, rather then the name of the job.
But I have created a routine that interrogates the sequence log and extracts the actual name of the failed job. It then goes to the log of this job and extracts the last DSJ.LOGFATAL entry and reports this one in the external log (as requested).

It works fine now. However I will have to figure out how to add an Invocation Id into the equation somehow. The jobs that may fail are multiple instance jobs and potentially there may be few of then running and writing to the job log.

[ray.wurlod]

As well as determining the job name from the Job activity name, you could alternatively scan the sequence's own log to get the job name.

[evee1]

I managed to connect to the actual job's log even if the job is multiinstance and get all the relevant information. So it is all solved!!!

[bashbal]

Unfortunately $ErrSource gives me the name of the activity within the sequencem, rather then the name of the job.
But I have created a routine that interrogates the sequence log and extracts the actual name of the failed job. It then goes to the log of this job and extracts the last DSJ.LOGFATAL entry and reports this one in the external log (as requested).

Would you be willing to post the code for your routine? I wish to send error notification via the exception handler. I have a routine that does this using a job name, but not with the Job Activity. Since you have already done this, you could save me some R&D time.