Transformation Guide

Back Next

Calling a Pre- or Post-Session Stored Procedure

You may want to run a stored procedure once per session. For example, if you need to verify that tables exist in a target database before running a mapping, a pre-load target stored procedure can check the tables, and then either continue running the workflow or stop it. You can run a stored procedure on the source, target, or any other connected database.

To create a pre- or post-load stored procedure:

Create the Stored Procedure transformation in the mapping.

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

Enter the name of the stored procedure.

If you imported the stored procedure, the stored procedure name appears by default. If you manually set up the stored procedure, enter the name of the stored procedure.

Select the database that contains the stored procedure in Connection Information.

Enter the call text of the stored procedure.

The call text is the name of the stored procedure, followed by all applicable input parameters in parentheses. If there are no input parameters, you must include an empty pair of parentheses, or the call to the stored procedure fails. You do not need to include the SQL statement EXEC, nor do you need to use the :SP keyword. For example, to call a stored procedure called check_disk_space, enter the following text:

check_disk_space()

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 check_disk_space required a machine name as an input parameter, enter the following text:

check_disk_space(oracle_db)

When passing a datetime value through a pre- or post-session stored procedure, the value must be in the Informatica default date format and enclosed in double quotes as follows:

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

You can use

CDI-PC

parameters and variables in the call text. Use any parameter or variable type that you can define in the parameter file. You can enter a parameter or variable within the call text, or you can use a parameter or variable as the call text. For example, you can use a session parameter, $ParamMyCallText, as the call text, and set $ParamMyCallText to the call text in a parameter file.

You must enter values instead of procedure parameters for the input parameters of pre- and post-session procedures.

Select the stored procedure type.

The options for stored procedure type include:

Source Pre-load.
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.

Source Post-load.
After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.

Target Pre-load.
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.

Target Post-load.
After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.

Select Execution Order, and click the Up or Down arrow to change the order, if necessary.

If you have added several stored procedures that execute at the same point in a session, such as two procedures that both run at Source Post-load, you can set a stored procedure execution plan to determine the order in which the Integration Service calls these stored procedures. You need to repeat this step for each stored procedure you wish to change.

Click OK.

Although the repository validates and saves the mapping, the Designer does not validate whether the stored procedure expression runs without an error. If the stored procedure expression is not configured properly, the session fails. When testing a mapping using a stored procedure, set the Override Tracing session option to a verbose mode and configure the On Stored Procedure session option to stop running if the stored procedure fails. Configure these session options on the Error Handling settings of the Config Object tab in the session properties.

You lose output parameters or return values called during pre- or post-session stored procedures, since there is no place to capture the values. If you need to capture values, you might want to configure the stored procedure to save the value in a table in the database.

Rename Saved Search

Table of Contents

Transformation Guide

Transformation Guide

Calling a Pre- or Post-Session Stored Procedure

Calling a Pre- or Post-Session Stored Procedure