An unconnected SQL transformation is an SQL transformation that is not connected to the mapping pipeline. Use an unconnected SQL transformation to call a stored procedure.
You use an Expression transformation to call the unconnected SQL transformation with an :SP expression. Or, you configure the SQL transformation to invoke a stored procedure before or after a mapping run. For example, you might use an unconnected SQL transformation to remove temporary source tables after the mapping receives data from the source.
You might also use an unconnected SQL transformation when you want to call a stored procedure multiple times in a mapping.
Calling an unconnected SQL transformation from an expression
Call an unconnected SQL transformation from an Expression transformation with an :SP expression.
When you call a stored procedure from an expression, you configure the expression to return the stored procedure output values to fields in the expression. Use one of the following methods to return the output values:
•Assign the output value to a local variable field.
•Assign the output value to the system variable PROC_RESULT.
When you use the PROC_RESULT variable, Data Integration assigns the value of the return parameter directly to the output field, which you can write to a target. You can also assign one output parameter to PROC_RESULT and the other parameter to a variable.
Use expression variables to access OUT or INOUT parameters in the stored procedure. If the stored procedure returns multiple output parameters, you must create variables for each output parameter.
Use the following syntax to call a stored procedure in an expression:
If the stored procedure returns a single output parameter or return value, use the reserved variable PROC_RESULT as the output variable.
For example, the following expression calls a stored procedure called GET_NAME_FROM_ID:
:SP.GET_NAME_FROM_ID(inID, PROC_RESULT)
inID can be either an input field in the stored procedure or a variable in the Expression transformation. When you run the mapping, Data Integration applies the value of PROC_RESULT to the output field for the expression.
If the stored procedure returns multiple output parameters, you must create expression variables for each output parameter. For example, if the stored procedure also returns a title, create a variable field called varTitle1 in the Expression transformation and use the field as the expression for an output field called Title. You write the following expression:
The following image shows how you configure the Expression transformation:
Data Integration returns output parameters in the order they are declared in the stored procedure. In this example, Data Integration applies the value of the first output field in the stored procedure to varTitle1 and passes it to the Title field in the Expression transformation. It applies the value of the second stored procedure output field to the output field for the expression.
The data types for the expression fields and variables must match the data types for the stored procedure input/output variables and return value.
Invoking a stored procedure before or after a mapping run
You can configure an unconnected SQL transformation to process a stored procedure before or after a mapping run. Data Integration invokes the stored procedure at the specified time. You do not need to call the stored procedure with an :SP expression. You can configure the stored procedure to run before or after the mapping receives data from the source, or before or after the mapping loads data to the target.
You can configure the following 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.
On the Advanced tab, configure the stored procedure type and enter the call text for the stored procedure. The call text is the name of the stored procedure followed by any applicable input parameters in parentheses. If there are no input parameters, you must include an empty pair of parentheses. Do not include the SQL statement EXEC or use the :SP keyword.
For example, to call the stored procedure Drop_Table, enter the following call text:
Drop_Table()
To pass a string input parameter, enter it without quotes. If the string has spaces in it, enclose the parameter in double quotes. For example, if the stored procedure Drop_Table requires a table name as an input parameter, enter the following call text:
Drop_Table(Customer_list)
Stored procedure run order
Specify the stored procedure run order when you have multiple source or target pre- or post-load stored procedures. Data Integration calls the stored procedures in the order that you specify. If you don't specify a run order, Data Integration calls the stored procedures in the order they are received at run time.
You can specify the stored procedure run order when the stored procedure type is set to anything except Normal and more than one stored procedure of that type exists in the mapping. For example, if you have multiple stored procedures that you want to call before Data Integration loads the source, you can specify the run order of the source pre-load stored procedures.
To set the stored procedure run order, open the Actions menu and select Stored Procedure Order.
The Stored Procedure Order dialog displays all source and target pre- and post-load stored procedures in the mapping even if there is only one stored procedure of a particular type. The tabs that you see in the Stored Procedure Order dialog depend on the type of stored procedures in the mapping. For example, if the mapping doesn't contain any target pre-load stored procedures, the Target pre-load tab doesn't display.
The following image shows the Stored Procedure Order dialog:
To set the stored procedure run order, select a stored procedure and use the up and down arrows to change the order. Data Integration calls the stored procedures from the top down.
You can't specify the run order for stored procedures with a stored procedure type of Normal or for connected stored procedures.
Unconnected SQL transformation example
You are updating employee salaries with a cost of living increase. You have a CSV file that contains employee names and IDs. You need to add each employee's salary, calculate their increase, and write the data to a new CSV file.
You have the following source data:
EMP_ID, EMP_NAME 1001, John 1002, Alice 1003, Mary 1004, Mark 1005, Stephan
You have a stored procedure called ADD_SALARY in an Oracle database that adds the employee's salary to the file and a second stored procedure called SALARY_INCREASE which calculates the salary increase. In the mapping, use two unconnected SQL transformations to call the stored procedures and two Expression transformations to call the SQL transformations.
The following image shows the mapping:
Configure the transformations in the following ways:
Source transformation
Configure the Source transformation to load the source data that you want to use.
SQL_Add_Salary transformation
Configure the first SQL transformation to call the ADD_SALARY stored procedure.
On the SQL tab, select the connection that contains the ADD_SALARY stored procedure and then select the stored procedure.
Select Unconnected stored procedure.
The stored procedure has one input field for the employee ID and returns the current salary in the output field.
SQL_Increase transformation
Configure the second SQL transformation to call the SALARY_INCREASE stored procedure.
On the SQL tab, select the connection that contains the SALARY_INCREASE stored procedure and then select the stored procedure.
Select Unconnected stored procedure.
The stored procedure has one input field for the employee name and returns the new salary in the output field.
Expression_Add_Salary transformation
Configure the first Expression transformation to call the SQL_Add_Salary transformation. Create a variable field for the input parameter and an output field to capture the output of the stored procedure.
On the Expression tab, add a variable field named ID and configure its value as the EMP_ID field in the source. Create an output field called salary to capture the return value of the ADD_SALARY stored procedure in the first SQL transformation. Configure the salary field to call the ADD_SALARY stored procedure with the following expression:
:SP.SQL_Add_Salary(ID, PROC_RESULT)
The expression takes the variable field ID as the input parameter of the stored procedure and returns the salary value to the SALARY output field.
The following image shows how you configure the Expression transformation:
Expression_Increase transformation
Configure the second Expression transformation to call the SQL_increase transformation. Add a variable field called CurrentSalary and configure its value as the incoming salary field. Add a output field called newSalary to capture the return value of the SALARY_INCREASE stored procedure. Configure the newSalary field to call the SALARY_INCREASE stored procedure with the following expression:
:SP.SQL_increase(CurrentSalary, PROC_RESULT)
The expression takes the variable field CurrentSalary as the input parameter of the stored procedure and returns the new salary to the newSalary output field.
The following image shows how you configure the Expression transformation:
Target transformation
Configure the Target transformation to create a target file at run time.
When you run the mapping, you get the following results:
