Stored Procedure Transformation

To use a Stored Procedure transformation, complete the following steps:
1. Create the stored procedure in the database.
2. Import or create the Stored Procedure transformation.
3. Determine whether to use the transformation as connected or unconnected.
4. If connected, map the appropriate input and output ports.
5. If unconnected, either configure the stored procedure to run pre- or post-session, or configure it to run from an expression in another transformation.
6. Configure the session.
The session properties in the Workflow Manager includes options for error handling when running stored procedures and several SQL override options.

If a database errors during a post-session stored procedureand  you configure the session to stop upon stored procedure error, the Integration Service fails the session.However, the Integration Service has already committed all data to session targets.

 

Specifying when the Stored Procedure Runs

¨ Normal  Connected stored procedures run only in normal mode.
¨ Pre-load of the Source. Before the session retrieves data from the source, the stored procedure runs. This is
useful for verifying the existence of tables or performing joins of data in a temporary table.
¨ Post-load of the Source. After the session retrieves data from the source, the stored procedure runs. This is
useful for removing temporary tables.
¨ Pre-load of the Target. Before the session sends data to the target, the stored procedure runs. This is useful
for verifying target tables or disk space on the target system.
¨ Post-load of the Target. After the session sends data to the target, the stored procedure runs. This is useful
for re-creating indexes on the database.

 

If the mapping calls more than one source or target pre- or post-load stored procedure in a mapping, the
Integration Service executes the stored procedures in the execution order that you specify in the mapping.

 

Configuring an Unconnected Transformation

¨ From an expression. Called from an expression written in the Expression Editor within another transformation
in the mapping.
¨ Pre- or post-session. Runs before or after a session.

 

When using an unconnected Stored Procedure transformation in an expression, you need a method of returning
the value of output parameters to a port. Use one of the following methods to capture the output values:
¨ Assign the output value to a local variable.
¨ Assign the output value to the system variable PROC_RESULT.
By using PROC_RESULT, you assign the value of the return parameter directly to an output port, which can apply
directly to a target. You can also combine the two options by assigning one output parameter as PROC_RESULT,
and the other parameter as a variable.

:SP.GET_NAME_FROM_ID(inID, PROC_RESULT)

 inID can be either an input port for the transformation or a variable in the transformation. The value of PROC_RESULT is applied to the output port for the expression.

:SP.GET_NAME_FROM_ID(inID, varOUTPUT1,PROC_RESULT)

The value of the second output port is applied to the output port for the expression, and the value of the first output port is applied to local variable varOUTPUT1 .

The output parameters are returned in the order they are declared inthe stored procedure. With all these expressions, the datatypes for the ports and variables must match the datatypes for the input/output variables and return value.

 

Calling a Pre- or Post-Session Stored Procedure

Double-click the Stored Procedure transformation, and select the Properties tab.

Enter the call text of the stored procedure,You do not need to include the SQL statement EXEC

check_disk_space(oracle_db)

SP(“12/31/2000 11:45:59”)

You can use PowerCenter parameters and variables in the call text. Use any parameter or variable type that
you can define in the parameter file.