Working with Stored Procedure Transformation in informatica

We have connected and unconnected in Stored procedure transformations in informatica

For connected stored procedure in informatica

CREATE OR REPLACE procedure SOURCE.sp_agg (in_deptno in number, max_sal out number,

min_sal out number, avg_sal out number, sum_sal out number)

As

Begin

select max(Sal),min(sal),avg(sal),sum(sal) into max_sal,min_sal,avg_sal,sum_sal

from emp where deptno=in_deptno group by deptno;

End;

/

Execute the above script in TOAD or Database

import stored procedure in mapping area directly connect to target.

For unconnected stored procedure in informatica

CREATE OR REPLACE procedure SOURCE.sp_unconn_1_value(in_deptno in number, max_sal out number)

As

Begin

Select max(Sal) into max_sal from EMP where deptno=in_deptno;

End;

/

Execute the above script in TOAD or Database

call stored procedure same as like unconnected lookup

use expression transformation for calling Unconnected stored procedure,

in functions tab in expression window