Oracle Stored procedure from DataStage Server job

kvnbabu · Post by **kvnbabu** » Tue Nov 25, 2008 5:56 am

Hi,

I have a stored procedure which takes two dates as input, calculates the no. of working days (by excluding weekends and holidays) and returns the number as the output. This stored procedure is available in Oracle database. Now, could you anybody please guide me in calling the stored procedure from DataStage server job?

I have searched through the topics already posted but i didn't find the anwer I am looking for.

Thanks in advance
KVN

chulett · Post by **chulett** » Tue Nov 25, 2008 8:35 am

What have you tried? Oddly enough, the Stored Procedure stage would be the first thing to try.

kvnbabu · Post by **kvnbabu** » Tue Nov 25, 2008 9:05 am

I can't find Stored Procedure stage in DataStage 7.5 Server Edition, but it meant for ODBC Stage, then Yes I tried this.

Following is things I have tried:
1. Imported Stored Procedure from Database with two input parameters and one output parameter.
2. In a server job, added one Oracle stage where I am taking two input dates from.
3. Oracle stage is connected to ODBC Stage, in the Input tab, the "Update action" is set to call stored procedure, and selected the stored procedure name, added two column names similar to the ones added to previous oracle stage. In the Output tab also, the "select using" is set to stored procedure and supplied the stored procedure name. In the clomuns tab, one row is added as per the OUT parameter and same thing in Prameter section.
4. The output lnik is connected to a flat file

I guess, I can notice one definite wrong thing is that, in the VIEW SQL tab of ODBC stage, the SQL for Input values looks like
{call GET_BUSINESS_DAYS(?,?)};
and the SQL for Input values looks like
{call GET_BUSINESS_DAYS(?)};
The question marks are to be replaced with the parameters and the SQL section was not editable and I am unable to connect them.

could you please guide me?

Thanks
KVN

chulett · Post by **chulett** » Tue Nov 25, 2008 9:14 am

Which "7.5" version do you have? Literally the first 7.5 release or one of the later minor releases like 7.5.1A or 7.5.2 or ?

It is an optional plug-in, perhaps whomever installed DS chose not to include it for some reason.

kvnbabu · Post by **kvnbabu** » Tue Nov 25, 2008 9:17 am

Its DataStage Designer version 7.5