SQL Transformation: Call Stored Procedures in Informatica Cloud (IICS)

1. SQL Transformation

SQL Transformation in Informatica Cloud is used to call a Stored Procedure or Function, or execute SQL queries midstream in a mapping pipeline. The SQL transformation can be used both as a connected and unconnected transformation.

Executing Stored Procedure or Function

To processes a stored procedure or function, the SQL transformation passes source data fields as values to the Input parameters of the stored procedure or function in the database, and then passes the return value or values to the output fields of the transformation.

Executing SQL Queries

To process SQL queries using SQL transformation, a pre-defined query can be chosen using a Saved Query component, or SQL statements can be entered manually into the SQL editor of the transformation.

In this article, we will focus on understanding the execution of Stored Procedure or Function using SQL transformation.

SQL Transformation in Informatica Cloud

2. Connected SQL Transformation

A Connected SQL transformation is an inline transformation which comes in the flow of the mapping. It passes incoming fields from an upstream transformation as input parameters to the stored procedure or function and returns the output parameters as data to the downstream transformation.

Problem Statement:

Below is the problem statement used for the demonstration of Connected SQL Transformation.

• The DEPARTMENTS table is considered as source.
• The salary details of employees are present in EMPLOYEES table.
• Both the tables can be joined using the field DEPARTMENT_ID.
• The requirement is to calculate the MIN, MAX and AVG salaries of employees in each department.

A Stored Procedure is created which takes department id as input parameter value and provides the MIN, MAX and AVG salaries of each department as return values.

CREATE OR REPLACE PROCEDURE SP_GET_SALARY_BY_DEPARTMENT(
	DEPT_ID IN NUMBER, 
	MAX_SALARY OUT NUMBER, 
	MIN_SALARY OUT NUMBER, 
	AVG_SALARY OUT NUMBER )
AS
BEGIN
	SELECT 
		MAX(SALARY), MIN(SALARY), ROUND(AVG(SALARY)) 
		INTO MAX_SALARY, MIN_SALARY, AVG_SALARY
	FROM EMPLOYEES 
  WHERE DEPARTMENT_ID = DEPT_ID
	GROUP BY DEPT_ID;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		MAX_SALARY := 0;
		MIN_SALARY := 0;
		AVG_SALARY := 0;
END;

Follow below steps to fulfil the above stated requirement using Connected SQL Transformation in a mapping.

1. In the source transformation, select the DEPARTMENTS table as the source object.

2. Pass the data from Source to SQL transformation.

3. In the Properties panel of SQL transformation, under SQL tab

• Select the database Connection where the Stored Procedure exists.
• Select the SQL Type as Stored Procedure.
• Click Select to select the stored procedure from the database.

4. The Input Fields tab displays the input parameter of the stored procedure.

5. Under Field Mapping tab, map the DEPARTMENT_ID from the source transformation to the input parameter of the stored procedure.

6. The Output Fields tab displays the output parameters of the stored procedure.

7. Pass the data from both Source transformation and SQL transformation to the Target transformation.

8. Configure the target, Validate and Save the mapping.

The below image shows the output of the mapping with MIN, MAX and AVG salary values against each department.

3. Unconnected SQL Transformation

An Unconnected SQL transformation is not connected to any transformation in a mapping. The unconnected SQL transformation can be called using :SP expression from multiple places in a mapping from an expression transformation. The expression can be configured to pass the stored procedure return values to output fields and variables.

The unconnected SQL transformation can also let you run the stored procedure before or after a mapping.

Problem Statement:

Below is the problem statement used for the demonstration of Unconnected SQL Transformation.

• The DEPARTMENTS table is considered as source.
• The salary details of employees are present in EMPLOYEES table.
• Both the tables can be joined used the field DEPARTMENT_ID.
• The requirement is to calculate the AVG salary of employees in each department.

A Stored Procedure is created which takes department id as input parameter value and provides the AVG salary of each department as return values.

CREATE OR REPLACE PROCEDURE SP_GET_AVG_SALARY_BY_DEPT(
	DEPT_ID IN NUMBER, 
	AVG_SALARY OUT NUMBER )
AS
BEGIN
	SELECT 
		ROUND(AVG(SALARY)) INTO AVG_SALARY
	FROM EMPLOYEES 
  WHERE DEPARTMENT_ID = DEPT_ID
	GROUP BY DEPT_ID;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
    AVG_SALARY := 0;
END;

Follow below steps to fulfil the above stated requirement using Connected SQL Transformation in a mapping.

1. In the source transformation, select the DEPARTMENTS table as the source object.

2. Drag and drop the SQL transformation on to mapping canvas.

3. In the Properties panel of SQL transformation, under SQL tab

• Select the database Connection where the Stored Procedure exists.
• Select the SQL Type as Stored Procedure.
• Click Select to select the stored procedure from the database.
• Select the Unconnected Stored Procedure check box.

4. The Input Fields tab displays the input parameter of the stored procedure.

5. The Output Fields tab displays the output parameters of the stored procedure.

6. Pass the data from Source to Expression transformation.

7. In the Expression transformation, create an output field named AVG_SALARY to capture the return value of the stored procedure.

8. Configure the field expression to call the unconnected SQL transformation using the following expression.

:SP.SQL_GET_AVG_SALARY(DEPARTMENT_ID, PROC_RESULT )

The expression takes the input field DEPARTMENT_ID as the input parameter of the stored procedure and returns the average salary value to the PROC_RESULT.

PROC_RESULT variable is used to capture the return value of the stored procedure and assigns the value to the output field.

9. Pass the data from Expression transformation to the Target transformation.

10. Configure the target, Validate and Save the mapping.

The below image shows the output of the mapping with AVG salary values against each department.

4. Unconnected SQL transformation calling Stored Procedure with multiple Output Parameters

The PROC_RESULT variable captures only one output parameter value returned by stored procedure and assigns it to the output field in which it is configured. The other output parameters returned by stored procedure can be captured by creating variable fields.

Consider if you are using the stored procedure SP_GET_SALARY_BY_DEPARTMENT  used in the demonstration of Connected SQL transformation in an Unconnected SQL transformation. You can capture MIN,MAX and AVG values as shown below.

Create two new variable ports for MIN and MAX Salary and use them in the stored procedure expression created for  AVG_SALARY output field.

The expression pushes the MIN and MAX values to the respective variable fields used in the expression and the PROC_RESULT captures the AVG salary value and assigns it to the output field.

The MIN and MAX values can be passed to the downstream transformations by creating two new output fields and assigning the variable fields used in the stored procedure expression as their values.

Note that the output parameters are returned in the order they are declared in the stored procedure.

5. Invoking a Stored Procedure before or after Mapping Run.

The Unconnected SQL Transformation can be configured to call a stored procedure before or after a mapping run so that the stored procedure is executed only once during the mapping run. This can be achieved by configuring the Stored Procedure Type in the advanced Tab of unconnected transformation. There is no need to call the SQL transformation using :SP expression.

The below modes can be configured for stored procedure types.

• Source Pre Load: The stored procedure runs before the mapping retrieves data from the source.
• Source Post Load: The stored procedure runs after the mapping retrieves data from the source.
• Target Pre Load: The stored procedure runs before the mapping sends data to the target.
• Target Post Load: The stored procedure runs after the mapping sends data to the target.

The Stored Procedure Type only defines the moment when the stored procedure should be executed. To call the stored procedure, you need to configure the Call Text.

In the Call Text, enter the stored procedure name followed by applicable input parameters in parentheses.

SP_DROP_TABLE(departments_temp)

If there are no input parameters, include empty pair of parentheses after stored procedure name.

SP_DROP_TABLE()

Note that string input parameters should be passed without quotes. If there are spaces in it, enclose them in double quotes.

6. Differences between Connected and Unconnected SQL Transformation

Both connected and Unconnected SQL transformation can perform below operations.

• Run a stored procedure every time a row passes through the SQL transformation.
• Pass parameters to the stored procedure and receive a single output parameter or multiple output parameters.

The below operations can be performed only by an Unconnected SQL transformation.

• Run a stored procedure before or after a mapping.
• Run a stored procedure once during a mapping.
• Run a stored procedure based on a condition using IIF statements.
• Call a stored procedure multiple times within a mapping.

SQL Transformation in Informatica Cloud

Subscribe to our Newsletter !!

Related Articles: