Informatica Stored Procedure Properties

Informatica Stored Procedure

------------------------------------------

Some of the properties of stored procedure transformation in Informatica are listed below

Location Information:

Use system variables $SOURCE or $TARGET or ODBS Data Source Name to define the location of Stored Procedures.

Stored Procedure Type:

• ·         Normal:

In this case it is a Connected Stored procedure that receives values from One or More Transformations and passes the value to Target Table or Other Transformations.

• ·         Source Pre Load:

It is Unconnected Mode and is used to perform Database Level task silently without any return value. This type of Stored Procedure is executed automatically when Data Query is about to start from Source Databases.

• ·         Source Post Load:

Same as Source Pre Load but it is executed automatically when Data Query is completed from source databases and Data Transformation is about to start.

• ·         Target Pre Load:

Same as Source Pre Load but it is executed automatically when Data Loading is about to start after Transformation.

• ·         Target Post Load:

Same as Source Pre Load but it is executed automatically when Data Loading into target Database is completed.

      

 Some of the uses of Unconnected Mode Stored Procedures are:

• To delete all records from Target Database,
• To make backup/copy of target database tables,
• To enable or disable database users before data loading
• To restore data from Backup Tables if data loading fails, etc.

Note: In the case of Unconnected Stored Procedure, we mostly use Procedures since procedures can be defined without any Return Value while Functions must return a value.

Call Text

Write the syntax to call Procedure or Function if the Stored Procedure is defined for other than 'Normal'. In case of PL/SQL procedures or functions, syntax will be:

            <Procedure name> [(<value list>)];

            <Function name> [(<value list>)];

 Note: Stored Procedure must have an Output Port to receive value.

Learn more on creating Stored Procedures Here