Stored Procedure Trans - created from Mind Map

Page 1

Passive

Connected

to populate and maintain databases

3 types of data that is passed between IS and SP

Input/Output parameters

Return Values

status codes

Provide Values / Receive Values

it can act similar to a single output parameter, or it may only return an integer value.

issues a status code that notifies whether or not it completed successfull

Unconnected

When is it run

Normal

Pre-load of the Source.

Post-load of the Source

Pre-load of the Target.

Post-load of the Target.

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

Return values

local variable.

system variable PROC_RESULT.

If neither, the port captures a NULL

assign the value of the return parameter directly to an output port,

Use only within an expression.

The parameters in the expression must include all of the parameters in the Stored Procedure transformation. You cannot leave out an input parameter. If necessary, pass a dummy variable to the stored procedure

TIPS

Reduce the row count. Use an active transformation prior to the Stored Procedure transformation to reduce the number of rows that must be passed the stored procedurE

Most of the logic used in stored procedures can be easily replicated using expressions in the Designer.

Parameter Types

IN -something passed to

OUT - something returned

INOUT - both input and output only Oracle

Stored Procedure Trans