Question about Calling oracle Stored proc in server job

[hsahay]

Datastage version : 7.5.1.1

Hi !

I have some questions about calling an oracle stored proc from datastage -


1. I have the Stored procedure stage(STP) installed. But it is grayed out for server job. Is this how it is supposed to be ? The documentation for STP does not mention that. Please note that the stage is available and usable for parallel jobs in our environment.

2. In the absence of STP stage i am using the ODBC stage to call a stored proc. It does not take any parameters and yet i could find no way of calling the stored proc without a parameter when using "Call stored proc" option in the "update actions" in the ODBC stage. I eventually had to change to "User defined SQL" so that i could edit the statement and remove the parameter. Is this how it works ?

3. If the stored procedure aborts for whatever reasons i want my server job to abort. Not finish successfully with just a warning message. Now obviously i don't want to enter all possible oracle error codes in the FATAL ERROR list in the Error handling tab of the odbc stage. So then what other option do i have ?

Thanks to all the Gurus for taking time to answer my query. It is always much appreciated.

[chulett]

1. Well, it is a optional plug-in stage for Server, so I'm guessing it could have been installed for PX and not the Server product. If you go to the Manager and open Stage Types / Server / Database is it listed there?

2. Yes, that's how it works.

3. Can't answer this one as I use the STP stage. And while it has an area for custom error code handling, I find it works just fine without me having to do anything special. As long as the proc raises an error when it has a problem, the job aborts. Suggest you build a small test job with a blowie-uppie test proc to check the ODBC functionality.