Stored Procedure Transformation is a passive transformation. Stored procedure transformation can be used in both connected and unconnected mode.
Stored procedures are stored and run within the database. Stored procedures contain a pre-compiled collection of PL-SQL statements. The stored procedures in the database are executed using the Execute or Call statements. Informatica provides the stored procedure transformation which is used to run the stored procedures in the database.
Some of the tasks you can do with stored procedures are listed below:
Check the status of a target database before loading data into it.
Determine if enough space exists in a database.
Perform a specialized calculation.
Dropping and recreating indexes.
The stored procedure must exist in the database before creating a Stored Procedure transformation, and the stored procedure can exist in a source, target, or any database with a valid connection to the Integration Service.
Stored Procedure Transformation Overview:
One of the important features of stored procedure is that you can send data to the stored procedure and receive data from the stored procedure. There are three types of data which pass between the integration service and the stored procedure:
Input / Output Parameters: Used to send and receive data from the stored procedure.
Return Values: After running a stored procedure, most databases returns a value. This value can either be user-definable, which means that it can act similar to a single output parameter, or it may only return an integer value. If a stored procedure returns a result set rather than a single return value, the Stored Procedure transformation takes only the first value returned from the procedure.
Status Codes: Status codes provide error handling for the Integration Service during a workflow. Stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. You cannot see this value.
Connected and Unconnected Stored Procedure Transformation:
Connected Stored Procedure Transformation: The stored procedure transformation is connected to other transformations in the flow of the mapping. Use connected Stored Procedure transformation when you need data from an input port sent as an input parameter to the stored procedure, or the results of a stored procedure sent as an output parameter to another transformation.
Unconnected Stored Procedure Transformation: The stored procedure transformation is not connected directly to the flow of the mapping. It either runs before or after the session, or is called by an expression in another transformation in the mapping.
Specifying when the Stored Procedure Runs:
The property, "Stored Procedure Type" is used to specify when the stored procedure runs. The different values of this property are shown below:
Normal: The stored procedure transformation runs for each row passed in the mapping. This is useful when running a calculation against an input port. Connected stored procedures run only in normal mode.
Pre-load of the Source: Runs before the session reads data from the source. Useful for verifying the existence of tables or performing joins of data in a temporary table.
Post-load of the Source: Runs after reading data from the source. Useful for removing temporary tables.
Pre-load of the Target: Runs before the session sends data to the target. This is useful for verifying target tables or disk space on the target system.
Post-load of the Target: Runs after loading data into the target. This is useful for re-creating indexes on the database.
