Post Process vs Post Condition

[Gerald Lewis]

Does one use Post Process scripting or Post Condition checking?

I have 3 SQL jobs in a workflow; all jobs execute Oracle package procedures - the first one returns an error_number (if set), the second raises an ORA error message (if error occurs), and the third job does some other processing   Here's example code;

-- First job process
DECLARE
     error_number   VARCHAR2(1);
BEGIN
     process_pkg.load_data(error_number);
END;

-- Second job process
BEGIN
     process_pkg.validate_data;
END;

-- Third job process
BEGIN
     process_pkg.process_data;
END;

What I would like to happen is; if the first job returns an error_number > 0 then abort the job and jobs 2 and 3 are blocked from running.   If an unexpected Oracle error occurs before the procedure completes that the job aborts also.   I want to be able to restart this first job after the issue has been fixed and allow jobs 2 and 3 a chance to execute.   If first job returns error_number <= 0 then proceed as normal with jobs 2 and 3.

When job 2 executes; if it returns any Oracle error (ORA) then abort job and job 3 is blocked from running.  Same as for the first job, I want to then be able to restart job 2 and continue.

Any suggestions on how I would check both error_number and possible ORA errors?

------------------------------
Newbie
------------------------------

[Toni Waechter]

Hi Gerald,

we use primarily post_processing. You can check the output of the own job by using PREP_PROCESS_REPORT and filter on known (!) error messages. It is possible to break the job on condition even it was running with rc=0 :

: MODIFY_STATE STATUS_TEXT = "Job failed"
: MODIFY_STATE RETCODE = "50"

And you can check the return code of the execution with GET_UC_OBJECT_STATUS. We use this sometimes on Oracle SQLLoader to set the job to ok, even if there are known load errors.

Best regards,

Toni

------------------------------
Administrator and Developer Jobautomation
BNP Paribas S.A. Niederlassung Deutschland
------------------------------

[Nicole Boeger]

Hi Gerald,

Toni is right, you can use filters or MODIFY_STATE to change the job status.

What you still need to do is to figure out whether your error_number is greater than 0; This number cannot easily be publicated to automic, you would need to print it in your Output of the PL/SQL block. And be sure that you get the output right.

To print the value of error_numer, put this line into your PL/SQL-Block:
dbms_output.put_line('Error-Number is' || error_number);

A better way is to check the value directly in your PL/SQL and raise an error, if applicable:
if error_number > 0 then
  raise_application_error(-20010,'An error occurred.');
end if;
You can use custom error numbers between -20000 and -20999.

You might also need to check for errors within your procedure:

Put something like this before the last statement "END;":

EXCEPTION

    WHEN NO_DATA_FOUND THEN

        raise_application_error(-20010,'An error occurred.');           

To catch all exceptions use:

EXCEPTION

    WHEN OTHERS THEN

Hope that helps.

------------------------------
Regards, Nicole
------------------------------

[Gary Chismar]

One option is to place them in separate workflows and then place the dependency between callings that is ANY_OK or ENDED_OK.  Else Block and send message.  Another is using scripting to do the same. set the return code = activate_uc_object.  If result > 0 do your next steps.  We have done both depending on circumstances.  Both work out fine.  I think, without knowing, is that you have a single JobS SQL object where you have all three sqls entered.  No shame in breaking them up to make life simple.

Good Luck.

------------------------------
Cheers,

Gary Chismar
Manager, Automic, PeopleSoft HCM/FI
Florida State University
Florida
------------------------------