call oracle stored procedure in datastage server job

[AmarpreetKaur]

I have one long 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 some mainframe tables (via db links) and loading data in oracle target tables.
It is a long procedure that has got cursors, execute immediate statements, delete stmt, truncate stmt and many more.
It is internally calling some functions to dump data in log tables also.
The procedure is doing fine when we are executing this through unix script or through oracle only.

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.

[ray.wurlod]

Welcome aboard.

What you seek to do is not possible in a server job.

[AmarpreetKaur]

thanks Ray! Is it possible through parallel jobs? Calling a procedure without any parameters and it will execute at backend...

[basu.ds]

I think you can try before /after sql query tab in oracle stage in server
eg.call storeprocedurename ;

[AmarpreetKaur]

I tried it using CALL ProcedureName, but it is giving me error: ORA-06576: not a valid function or procedure name. This procedure is working fine at oracle side. I even tried all the suggestions menioned on here.

Any other siggestion?

[chulett]

It does work fine 'Before SQL' in a Server OCI stage. Are you connecting as the owner? Or code in a dummy parameter and call it from the SP stage.

[Mike]

It is not taking any parameters and not returning anything.

So what's the rationale for running this stand-alone stored procedure from a server job?

Why not just run it from a job sequence if you simply want its execution controlled by DataStage?

Mike

[kandyshandy]

Good one Mike.

You can code in dummy parameter and call it from SP stage (as Craig suggested) or call it from ODBC stage...

[macromathi1]

oracle procedures very well can be called from before/after sql of oracle stage. check for the connection strings and qualify the procedure with the right schema..