Stored Procedure Transformation

The Stored Procedure Transformation feature facilitates you to define complex transformations involving multiple tables which are contained in a pre-defined stored procedure/function. The recommended method is to use CALL <function name>, provided the function is present in the Atomic Schema.

To define a Stored Procedure Transformation:

1. Select Stored Procedure from the Type drop-down list in the PLC Type pane.
2. Add the parameters as explained in the Adding Parameter Definition section.

   Figure 7-27 Stored Procedure Editor pane

   
   

   

   
   
3. In the Stored Procedure Editor field, enter the CALL function to invoke the function stored in the Atomic Schema. You can also enter the SQL block of the stored procedure/function. Ensure that all the parameters used in your stored procedure are added from the Parameter Definition grid. Every function you create should contain BatchID (VARCHAR2) and MisDate (VARCHAR2) as the first two parameters.

   Note:

   In case of CALL function, do not add BatchID (VARCHAR2) and MisDate (VARCHAR2) as Parameters from the Parameter Definition grid since these two mandatory parameters are appended while creating the procedure.

   If you want to pass Task_ID or Infodom name to the stored procedure/function, define a parameter and explicitly pass the parameter value as TASKID or INFODOM from ICC or RRF. During execution, TASKID will be replaced with the task ID and INFODOM will be replaced with the Information Domain name.

4. (Optional ) Click Check Syntax (adjacent to the Save button) to check the syntax of the stored procedure.
5. Click Save to save the Stored Procedure Transformation definition.