Stored Procedure Transformation

Stored Procedure Transformation

A stored procedure is a precompiled collection of Transact-SQL, PL-SQL or other database procedural statements and optional flow control statements, similar to an executable script. Stored procedures are stored and run within the database.

You might use stored procedures to complete the following tasks:

--> Check the status of a target database before loading data into it.

--> Determine if enough space exists in a database.

--> Perform a specialized calculation.

--> Drop and recreate indexes.

Stored procedures also provide error handling and logging necessary for critical tasks.

You might use a stored procedure to perform a query or calculation that you would otherwise make part of a mapping.

There are three types of data that pass between the Integration Service and the stored procedure:

Input/Output Parameters

For many stored procedures, you provide a value and receive a value in return. These values are known as input and output parameters.

Return Values

The Stored Procedure transformation captures return values in a similar manner as input/output parameters, depending on the method that the input/output parameters are captured.

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. The stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. The Integration Service uses it to determine whether to continue running the session or stop.

Connected Stored Procedure

The flow of data through a mapping in connected mode also passes through the Stored Procedure transformation. All data entering the transformation through the input ports affects the stored procedure. You should use a 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.

We can go for Connected when we want to

--> Run a stored procedure every time a row passes through the Stored Procedure transformation.

--> Pass parameters to the stored procedure and receive a single output parameter.

--> Pass parameters to the stored procedure and receive multiple output parameters.

Un Connected Stored Procedure

The unconnected 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.

We can go for Connected when we want to

--> Run a stored procedure before or after a session.

--> Run a stored procedure once during a mapping, such as pre- or post-session.

--> Run nested stored procedures.

--> Call multiple times within a mapping.

Stored Procedure Types

Normal. The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. This is useful for calling the stored procedure for each row of data that passes through the mapping, such as running a calculation against an input port. 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.

You can run more than one Stored Procedure transformation in different modes in the same mapping. For example, a pre-load source stored procedure can check table integrity, a normal stored procedure can populate the table, and a post-load stored procedure can rebuild indexes in the database.

--> Stored Procedure transformations are created as Normal type by default, which means that they run during the mapping, not before or after the session.

-->If stored procedures exist in the database that do not contain parameters or return values, they appear in a folder called PROCEDURES otherwise in the FUNCTIONS folder.

Configuring an Unconnected Transformation

When using an unconnected Stored Procedure transformation in an expression, you need a method of returning the value of output parameters to a port.

--> 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.

Use PROC_RESULT only within an expression. If you do not use PROC_RESULT or a variable, the port containing the expression captures a NULL. You cannot use PROC_RESULT in a connected Lookup transformation or call text.

If the stored procedure returns a single output parameter or a return value (but not both), you should use the reserved variable PROC_RESULT as the output variable. In the previous example, the expression would appear as:

: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.

If the stored procedure returns multiple output parameters, you must create variables for each output parameter. For example, if you create a port called varOUTPUT2 for the stored procedure expression, and a variable called varOUTPUT1, the expression appears as:

:SP.GET_NAME_FROM_ID(inID, varOUTPUT1, PROC_RESULT)

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.

Stored Procedure Properties --> Call Text

This 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.

For example, to call a stored procedure called check_disk_space, enter the following text:

check_disk_space()

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”)

Error Handling

Sometimes a stored procedure returns a database error, such as “divide by zero” or “no more rows.” The final result of a database error during a stored procedure depends on when the stored procedure takes place and how the session is configured.

You can configure the session to either stop or continue running the session upon encountering a pre- or post-session stored procedure error. By default, the Integration Service stops a session when a pre- or post-session stored procedure database error occurs.

Session --> Config Object --> On Stored Procedure error --> Stop, Continue

If the database returns an error for a particular row, the Integration Service skips the row and continues to the next row. As with other row transformation errors, the skipped row appears in the session log.

Notes

--> A single output parameter is returned using the variable PROC_RESULT.

--> When you call a stored procedure in an expression, use the :SP reference qualifier.

--> Use PROC_RESULT to apply the output parameter of a stored procedure expression directly to a target. You cannot use a variable for the output parameter to pass the results directly to a target. Use a local variable to pass the results to an output port within the same transformation.

--> Nested stored procedures allow passing the return value of one stored procedure as the input parameter of another stored procedure. For example, if you have the following two stored procedures:

−get_employee_id (employee_name)

−get_employee_salary (employee_id)

And the return value for get_employee_id is an employee ID number, the syntax for a nested stored procedure is:

:sp.get_employee_salary (:sp.get_employee_id (employee_name))

--> Do not use single quotes around string parameters. If the input parameter does not contain spaces, do not use any quotes. If the input parameter contains spaces, use double quotes.