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 |
Go to Function tab of the Expression Editor, go to Stored procedures folder and Dbl click on T_SP_UNC
 |
| 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 |
In HLF_YR_SAL formula, assign the variable hlf_sal. (Confirm)
 |
| 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 |
Connections:
Source
Target
Stored Procedure
Define the workflow.
Assign source,target and transformation (sp) connections.
Start the workflow.
Preview
 |
| stored procedure transformation |
Comments
Post a Comment