passing arguments to the stored procedure in DS

kalpna · Post by **kalpna** » Thu Oct 12, 2006 8:51 pm

Hi! Evryone,

I have a table/file containing a list of policy_nos and I have a stored procedure, which takes this policy_no as a parameter to generate some output.
so, my question is, how can I implement it in DS?
I Need to call the procedure for ecah policy_no in the list.

Any suggestions?

Thanks
Kalpana.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 12, 2006 9:05 pm

Import the Stored Procedure definition into DataStage. This will give you the columns corresponding to its arguments.

Call the stored procedure (from an ODBC stage, etc., or from a Stored Procedure stage).

dh_Madhu · Post by **dh_Madhu** » Fri Oct 13, 2006 12:44 am

Hi! Ray,
I think,
the problem is not executing the stored procedure. but, how should I pass more than one policy number into stored procedure...
I mean, calling stored procedure for every policy....!!!

how is that possible?

kalpna · Post by **kalpna** » Fri Oct 13, 2006 5:14 am

BAsically my question is, How can we call a stored procedure repititively with different arguments?

Using a stored procedure stage, how can we pass a dynamic parameter?

How can we implement this in DS?

kumar_s · Post by **kumar_s** » Fri Oct 13, 2006 5:39 am

Select the table that contain the policy_no thorugh a oracle stage and link it to the ODBC stage which calls the stroed procedure. The output of the OCI will the input for the ODBC.

chulett · Post by **chulett** » Fri Oct 13, 2006 7:38 am

For that, you could use the Stored Procedure stage rather than ODBC. It can be used like a transformer, so to speak, on a row by row basis.

What version of DataStage? What database?