Out parameters in DataStage

Anand K · Post by **Anand K** » Tue May 18, 2004 7:20 am

Hi All..

I have a procedure which has 2 in parameters and 4 out parameters.
These 4 out parameters are neither inserted in a table nor captured in a file.
Please suggest me a method by which the 4 out parameters in the stored procedure (source) can be captured in a table or file.
It will be of great help if I could know this can be done through ORAOCI8 Stage itself..
Thnx in advance

chulett · Post by **chulett** » Tue May 18, 2004 7:28 am

I really don't believe that DataStage supports out parameters in a Stored Procedure. Perhaps in the future when the much delayed Stored Procedure Stage makes its debut, but not now.

Search the forum for 'stored procedure', I seem to recall several prior conversations on this topic... but don't recall any resolution other than rebuilding the functionality of the proc directly into a DS job.

roy · Post by **roy** » Tue May 18, 2004 7:40 am

Hi,
Craig is right.
If you insist on having the logic in a Stored Procedure the only thing close to getting what you want will be to invoke the SP in a before sql statement in a plugin stage, have the SP build or populate a table, use the output link to get the data and in an after sql statement issue a dorp table or another SP that will cleanup the temp data you put in the first SP.

or

input the rows you want to a table (temp in nature not real temp table) run in a after sql the SP to populate another table with the results and then read the data in an output link.

(you get the general idea I hope)

I for one prefer rewriting the logic in ds when possible.

IHTH,