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
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:
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.
0 comments:
Post a Comment