Stored Procedure

[sheema]

I have a server job,where in i have a ORAOCI stage,a transformer and a ORAOCI stage as lookup.My requirement is that i need to call a stored procedure in the ORAOCI stage(lookup),this procedure takes 3 input values and returns a output value.These input values have to be selected from the oracle database dynamically and give an output value.I have to use these 4 values to lookup with the other ORAOCI(source).

I tried using stored procedure stage,but it says Stored procedure stage does not act as a lookup.I cannot load the data into hash file also as the data is more than 15 million rows.we might run into hash file size problems.

Any ideas how to do this.

[rameshrr3]

A stored procedure stage does not support reference links.

What you can do is to remove the transformer stage and connect a stored procedure stage to the input from OCI stage.
Forward Row data has to be selected,and the procedure has to be selected for each row.

You can refer the Stored Procedure stage documentation in the section "Emulating Refernce Links on page 30" which elucidates the use of STP stage for performing Lookups.

You can access this document via the plug-in bookshelf . Name of file is "stpstage.pdf"

Thanks
Ramesh

[NBALA]

Hi !

You can use the ORAOCI itself for calling your store procedure. you can call stored procedure in ORAOCI stage ->OUPUT/INPUT -> SQL->after (or) before

Use

Code: Select all

CALL SP_YOURSP(<parameters>)

-NB