Call stored procedure in Sequence

[sheema]

Hi,
Can we call a Oracle Stored procedure in Job sequences .

[g_rkrish]

You have to try with execute command activity..with creating a small unix script that will connect to the database and execute the stored procedure...I feel in oracle you run a stotred procedure in command prompt....

[chulett]

In other words, a scripted call to sqlplus with the script launched by the Execute Command stage. Make no assumptions about the runtime environment and ensure it works outside of DataStage first, then add it to your Sequence. Also make sure errors are properly reported back from your proc to sqlplus to the script and lastly passed back to DataStage from the script's exit status - otherwise when it fails your Sequence job will have no clue.

[sachin1]

you have to create a script that will call your procedure in a file and call it in Execute_Command Stage like

for windows env:(try for unix env)

sqlplus -s oracle/oracle@BIODS @d:\datastage\data\india1\sachin\in\test.sql;

where test.sql contains

begin
test_procedure;
end;
/
exit

[sheema]

Thanks for all the inputs.

[praveensuss]

You can use "Stored Procedure stage" available with DataStage to call the stored procedures and sequence them through a Job Sequencer..I hope this shld work..