Informatica Interview Questions on Stored Procedure Transformation

1. What is a stored procedure?

 

A stored procedure is a precompiled collection of database procedural statements. Stored procedures are stored and run within the database.

 

2. Give some examples where a stored procedure is used?

 

The stored procedure can be used to do 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.

 

3. What is a connected stored procedure transformation?

 

The stored procedure transformation is connected to the other transformations in the mapping pipeline.

 

4. In which scenarios a connected stored procedure transformation is used?

·         Run a stored procedure every time a row passes through the mapping.

·         Pass parameters to the stored procedure and receive multiple output parameters.

 

5. What is an 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.

 

6. In which scenarios an unconnected stored procedure transformation is used?

·         Run a stored procedure before or after a session

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

·         Run a stored procedure based on data that passes through the mapping, such as when a specific port does not contain a null value.

·         Run nested stored procedures.

·         Call multiple times within a mapping.

 

7. What are the options available to specify when the stored procedure transformation needs to be run?

 

The following options describe when the stored procedure transformation runs:

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

 

A connected stored procedure transformation runs only in Normal mode. A unconnected stored procedure transformation runs in all the above modes.

 

8. What is execution order in stored procedure transformation?

 

The order in which the Integration Service calls the stored procedure used in the transformation, relative to any other stored procedures in the same mapping. Only used when the Stored Procedure Type is set to anything except Normal and more than one stored procedure exists.

 

9. What is PROC_RESULT in stored procedure transformation?

 

PROC_RESULT is a system variable, where the output of an unconnected stored procedure transformation is assigned by default.

 

10. What are the parameter types in a stored procedure?

 

There are three types of parameters exist in a stored procedure:

·         IN: Input passed to the stored procedure

·         OUT: Output returned from the stored procedure

·         INOUT: Defines the parameter as both input and output. Only Oracle supports this parameter type.