PL/SQL Procedure to be called after datastage job.

[shreya27]

Hi All,

Is it possible to call a PL/SQL Procedure after a datastage job is executed? pls let me know.




Thanks

[varaprasad]

yes it is

Wrap your PL/SQL code in a Unix shell script and call from After Job subroutine.

[shreya27]

yes it is

Wrap your PL/SQL code in a Unix shell script and call from After Job subroutine.

Can you pls provide the syntax to wrap the plsql code in unix shell script?

[varaprasad]

yes it is

Can you pls provide the syntax to wrap the plsql code in unix shell script?

Are you serious??

Okay, Firstly you have not provided the full details of your environment mainly the Database.

But the below snippet may help you, if your database is Oracle.

#!/bin/sh

sqlplus -s username/password<<END
execute procedure_name( ..parameters..)
commit;
exit;
END

[deb033]

I think very simply we can call the SP after the job.

Make one job with some table def. stage, pass some dummy column and the SP being called in the after sql part of it. then just link it to some dummy seq file.

Now in a sequencer, link this job after the main job. I hope this can solve the purpose.

[chulett]

Or simply use the Stored Procedure stage.