Abort job if the Before/After Subroutine fails

clmhwyfe · Post by **clmhwyfe** » Mon May 18, 2015 2:32 pm

Hi All,

Am using a before and after routine to execute a PL/SQL block that updates the job log information to a oracle table.

Is there a way to abort the job incase the before or after sub routine fails , as my job runs even when the before routine fails.

Am using the ExecSH routine to call the PL/SQL block via sqlplus command, and also the sqlplus command outputs the password in the director log even when it is defined as a encrypted value.

Thanks

chanaka · Post by **chanaka** » Mon May 18, 2015 3:12 pm

Is it giving you a warning if execSH fails?

chulett · Post by **chulett** » Mon May 18, 2015 3:53 pm

That's the thing, the "ExecSH" part typically doesn't fail. It successfully executes and successfully runs sqlplus, it's just the procedure itself inside the sqlplus session that fails. You'll need to do two things - make sure the the script detects the errors in the sqlplus session and then ensure the script passes back a non-zero return code to DataStage when that happens. Then the job will know it should abort.

ray.wurlod · Post by **ray.wurlod** » Mon May 18, 2015 3:57 pm

If your script returns a non-zero exit status, then ExecSH will pick that up and cause the job to abort. Source code for ExecSH can be found in the Routines branch of the repository. You can clone it and adapt the copy for your own specific purposes if desired.

clmhwyfe · Post by **clmhwyfe** » Mon May 18, 2015 4:22 pm

Thanks all,

I changed the return code to a 1 and It resolved the issue.

abc123 · Post by **abc123** » Fri Apr 22, 2016 9:55 pm

I am assuming that in the "Input Value" of the "Before-job subroutine", you put in something like this:

sqlplus userid/pwd@server MySql.sql;

Can you tell me how you returned a 1 from the sqlplus call?

ray.wurlod · Post by **ray.wurlod** » Sat Apr 23, 2016 2:48 am

Code: Select all

sqlplus...
exit $?