Using stored procedure in Datastage
Moderators: chulett, rschirm, roy
Using stored procedure in Datastage
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
output arguments or return values. Can anyone please suggest me which stage touse any inputs are really appreciated
Thanks
Re: Using stored procedure in Datastage
you can use the sp stage for the same and capture the output of the sp into sequential file.
Mayura
Re: Using stored procedure in Datastage
SP stage can be used and to get count get rowcount using transformes
Re: Using stored procedure in Datastage
Thank you so much for the response. I have used the STP but Im getting the errorRagupathy wrote:SP stage can be used and to get count get rowcount using transformes
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
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
-
- Premium Member
- Posts: 29
- Joined: Tue Oct 21, 2008 9:26 am
- Location: Samsula, FL
- Contact:
using an ODBC stage to execute a stored procedure
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.
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.