Developer Transformation Guide

10.4.0
- 10.5.2
- 10.5
- 10.4.1

Back Next

Stored Procedure Result Sets

You can configure a SQL transformation to receive a result set from a stored procedure. A stored procedure returns multiple rows in a result set. The SQL transformation can return each row to the mapping.

The following stored procedure returns a result set:

CREATE OR REPLACE FUNCTION fetchEMPinfo 
(p_State  IN VARCHAR2 ) 
return types.cursortype
AS
my_cursor types.cursortype;
BEGIN
OPEN my_cursor FOR SELECT EMP_ID, NAME, CITY FROM EMP WHERE STATE = p_State ORDER BY EMP_ID;
RETURN my_cursor;
END;

When you import the stored procedure, the Developer tool creates a stored procedure call statement similar to the following syntax:

call
FETCHEMPINFO (?P_STATE?)

The input parameter is p_state. The Developer tool does not create the output ports for you. You must manually create the output ports with the same datatypes as the stored procedure parameters.

For example, the resultSet contains the EMP_ID, EMPNAME, and CITY columns. Create output ports for these columns.

You must also manually update the SQL call with the output columns using the following syntax:

(?EMP_ID?,?EMPNAME?,?CITY?) = call
FETCHEMPINFO (?P_STATE?)

Stored Procedures

Result Sets with Different Databases

Result Set Rows

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal