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.
Question about Calling oracle Stored proc in server job
Moderators: chulett, rschirm, roy
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers