DSXchange

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

Is it giving you a warning if execSH fails?

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.

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.

Thanks all,

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

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?