Using stored procedure in Datastage

[balu1625]

Im in the initial stage of learning datastage. I have a stored procedure which takes input as a date.Data_out is a cursor type. In datastage I just need to get the counts of those out records of the result set of the stored procedure. Can I use the ODBC stage or STP stage. I have this doubt bcoz documentation states that if you use ODBC stage it does not support
output arguments or return values. Can anyone please suggest me which stage touse any inputs are really appreciated

Thanks

[mayura]

you can use the sp stage for the same and capture the output of the sp into sequential file.

[Ragupathy]

SP stage can be used and to get count get rowcount using transformes

[balu1625]

SP stage can be used and to get count get rowcount using transformes

Thank you so much for the response. I have used the STP but Im getting the error
Stored_Procedure_34: First output column must be a Procedure return code.
I'm not sure which option Im not specifying it right. Im following the Datastage developers guide

[vinothkumar]

Do a exact search on the error message in this forum.

[balu1625]

Do a exact search on the error message in this forum.

I have already tried that and there was one msg but there was no answer provided

[vinothkumar]

Check the value for Procedure return code in Output->Properties of Stored procedure stage. If it yes, then change it to NO and then try.

[qutesanju]

let me know what data base you are using ?
SQL server or oracle?

I strongly suggests to ODBC stage for SQL server 2000,2005

[jacksamsula]

We use the ODBC stage in order to execute procedures written in PL/SQL on an Oracle server.

Of course, a DSN must be defined on the server which hosts the procedure.

If the only function of the Datastage is to execute the call then we use a dummy ODBC stage to feed a dummy column to the calling ODBC stage, i.e. the dummy stage uses the table name DUAL, the DSN of the host machine as the Data Source Name, a SQL Statement "SELECT 'Dummy' FROM DUAL", and a dummy column - and a link to the ODBC stage making the call.

The stage making the call, of course, also uses the same DSN name as the Data Stage Name, the update action is "User Defined SQL", and the SQL is the call to the proc e.g. "{call package_name, my_proc};"

Pardon my being so explicit, but sometimes the devil is in the details with something like this.