✍ Kashif M
Trends

Monday, 9 September 2013


unconnected stored procedure transformation in informatica


By on September 09, 2013





Assume we need two output parameters AN_SAL and Half_yearly_sal. INPUT parameter is one here SAL.



SQL> CREATE OR REPLACE PROCEDURE CALC_AN_HLF_YR_SAL(P_SAL in EMP.SAL%TYPE,P_AN_SAL OUT NUMBER,P_HLF_SAL OUT NUMBER) is

BEGIN

P_AN_SAL:=P_SAL*12;

P_HLF_SAL:=P_SAL*6;

END;



DEFINE A MAPPING M_SP_UNC. Drag Source,Target EMP_SP_UNC and create two transformations: Expression T_EXP_SP_UNC and Stored procedure T_SP_UNC.

Project the required ports from Source Qualifier to the Expression Transformation.

Edit the Expression Transformation and add two new port AN_SAL (change datatype, precision and scale), HLF_YR_SAL .

Also add one port hlf_sal which is a variable port.



stored procedure transformation
stored procedure transformation




Go to Function tab of the Expression Editor, go to Stored procedures folder and Dbl click on T_SP_UNC


stored procedure transformation
stored procedure transformation




Assign the parameters SAL (IN), PROC_RESULT (Informatica Global Variable) and hlf_sal (local variable).

:SP.SP_UNC(SAL,PROC_RESULT,hlf_sal)




Note: PROC_RESULT captures the first output parameter. 

If we use some local variable instead of PROC_RESULT, it gives an error.

Example if I use the formula

:SP.SP_UNC(SAL,hlf_sal,hlf_sal)

stored procedure transformation
stored procedure transformation







In HLF_YR_SAL formula, assign the variable hlf_sal. (Confirm)


stored procedure transformation
stored procedure transformation




Project required ports from Expression Transformation to TGT.

NOTE: Unconnected Stored Procedure Transformation is used to project more than one output parameter.

stored procedure transformation
stored procedure transformation









Connections:

Source

Target

Stored Procedure

Define the workflow.

Assign source,target and transformation (sp) connections.

Start the workflow.

Preview

stored procedure transformation
stored procedure transformation





Kashif
mkashu: unconnected stored procedure transformation in informatica
Review : Kashif | Kashif
Update: September 09, 2013 | Rating: 4.5

Comment for "unconnected stored procedure transformation in informatica"

0 comments

Post a Comment

Blog Archive