Datstage solutions, knowledge base, FAQ and best practices

2.5. How to invoke an Oracle PLSQL stored procedure from a server job

To run a pl/sql procedure from Datastage a Stored Procedure (STP) stage can be used.
However it needs a flow of at least one record to run.

It can be designed in the following way:

• source odbc stage which fetches one record from the database and maps it to one column - for example: select sysdate from dual
• A transformer which passes that record through. If required, add pl/sql procedure parameters as columns on the right-hand side of tranformer's mapping
• Put Stored Procedure (STP) stage as a destination. Fill in connection parameters, type in the procedure name and select Transform as procedure type. In the input tab select 'execute procedure for each row' (it will be run once).

Design of a DataStage server job with Oracle plsql procedure call