How to call oracle stored procedure in datastage server job
Posted: Tue Jul 03, 2012 9:40 am
Before writing this,I had searched on this on the forum,but couldnot find the exact solution.
I have one oracle procedure which I want to execute via datastage server job. It is not taking any parameters and not returning anything. It is extracting data from one table and doing some functionality(looping) and loading data in oracle target table.
The procedure is doing fine when we are executing this through through oracle.
I want to know how to execute this procedure in datastage server job. Just want to call it and execute it in datastage without passing any parameter.
Soultion I had tried :
I had tried to run this in the "After SQL" (also on "Before SQL") tab in OCI stage.(eg: Call procedurename,execute procedurename)---- It didnot work.It gave errors.(ORA-06576: not a valid function or procedure name,ORA-00900: invalid SQL statement)
Note : we are using Datastage server 7.5v on windows server and we donot have the Stored Procedure plugin as well.
Thanks
Kiran
I have one oracle procedure which I want to execute via datastage server job. It is not taking any parameters and not returning anything. It is extracting data from one table and doing some functionality(looping) and loading data in oracle target table.
The procedure is doing fine when we are executing this through through oracle.
I want to know how to execute this procedure in datastage server job. Just want to call it and execute it in datastage without passing any parameter.
Soultion I had tried :
I had tried to run this in the "After SQL" (also on "Before SQL") tab in OCI stage.(eg: Call procedurename,execute procedurename)---- It didnot work.It gave errors.(ORA-06576: not a valid function or procedure name,ORA-00900: invalid SQL statement)
Note : we are using Datastage server 7.5v on windows server and we donot have the Stored Procedure plugin as well.
Thanks
Kiran