Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(3 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Use a Stored Procedure as a source for a PowerCenter mapping
Solution
A Stored Procedure can be called from the Source Qualifier as long as a result set is returned from the Stored Procedure.
To use a stored procedure as a source in a PowerCenter mapping, do the following:
  1. Create a stored procedure in a supported database that returns a result set.

    Example

    The following DB2 Stored Procedure can be called from Source Qualifier:

CREATE PROCEDURE ARJUN.PROCEDURE2 (in field_in char(10),out field1_out int,out field2_out char(10))
DYNAMIC RESULT SETS 1
P1: BEGIN
--DECLARE field1_outINTDEFAULT 0;
--DECLARE field2_outCHAR(10)DEFAULT '0000000000';
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT field1, field2 FROM tstsp1 where field2 <> field_in;
-- Cursor left open for client application
OPEN cursor1;
--select field1,field2 into field1_out,field2_out from tstsp1 where field2 = field_in;
select field1 * 100 ,field2 into field1_out, field2_out from tstsp1 where field2 = field_in;
END P1

  1. Note

    This procedure returns a cursor as output so it can be called in a Source Qualifier.
    This procedure also has two output parameters. If the procedure only has return parameters but does not return a result set, then it cannot be called from a Source Qualifier.

  2. Add a statement to call the stored procedure in the Source Qualifier SQL Override.

    Example

    The following SQL statement can be used for the this DB2 procedure is:

    Call procedure2 (field1)

    Note

    The input parameter is provided but not the output parameter, as it cannot be captured in the Source Qualifier.

The return out parameter cannot be captured from a Stored Procedure in a Source Qualifier SQL override. You can only capture a result set,
from a Stored Procedure, called from a Source Qualifier.
A procedure that returns both a result set and a value can be used without the out parameters.
More Information

A Stored Procedure that returns a result set cannot be called from Stored Procedure transformation. A value can be returned but not a result set.

Reference
Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:4/28/2011 1:18 AMID:19611
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)