How to call oracle stored procedure in datastage server job

[kirankumarreddydesireddy]

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

[ray.wurlod]

Perhaps you need to qualify the SP name with its schema name, so that it is unambiguously indentified.

[Kryt0n]

And does the user executing the command from DataStage have the authority to execute the procedure?

[ray.wurlod]

I'd expect a different error message in that case.

[Kryt0n]

More in the permission to see the procedure than actually execute it. As in they possibly qualified with a schema name but weren't granted the appropriate permissions to that schema

What's in my head isn't necessarily what my hands type...

[kirankumarreddydesireddy]

Perhaps you need to qualify the SP name with its schema name, so that it is unambiguously indentified. ...

Hi Ray,

We had called this SP in the After SQL tab with the schema name as well.

i.e CALL schemaname.Procdurename,execute schemaname.Procedurename.

We had also given the execute permission to the user,with which we are running this SP.

It gave us the same error mentioned above.


Thanks
Kiran

[kirankumarreddydesireddy]

Hi,

Finally,I was successfully able to run the store procedure in OCI stage in datastage server job.

In the After SQL tab,

Call schemaname.procedurename();

We need to include open and closed braces,even though we dont have the arguments.


Thanks
Kiran

[ray.wurlod]

The empty parentheses signify that the list of arguments is zero long.

[chulett]

Note : we are using Datastage server 7.5v on windows server and we donot have the Stored Procedure plugin as well.

You do. You may have chosen not to install it but you should certainly have it as an option.