Developer Transformation Guide

10.4.0
- 10.5.2
- 10.5
- 10.4.1

Back Next

Stored Procedure Example

You can call a stored procedure that returns data to the SQL transformation.

The following stored procedure receives an employee number and returns one row with the employee number and the employee name:

CREATE OR REPLACE PROCEDURE SP_GETNAME
(IN_EMPNO IN NUMBER, OUT_EMPNO NUMBER, OUT_NAME OUT STRING) 
AS
BEGIN
SELECT EMP_KEY,EMP_NAME into OUT_EMPNO , OUT_NAME from EMP_TABLE where EMP_KEY=IN_EMPNO;
END;/"

To create the SQL transformation, import the stored procedure. The Developer tool creates the input ports and the output ports. The port names are the same as the parameter names in the stored procedure.

The following figure shows the ports for the SQL transformation:

The Overview view shows an IN_EMPNO input port and the SQLError, Out_empno, and Out_Name output ports.

The Developer tool creates the following stored procedure call to retrieve the employee name:

call
SP_GETNAME (?IN_EMPNO?,?OUT_EMPNO?,?OUT_NAME?)

You can view the stored procedure call in the SQL Editor. Any SQL errors appear in the SQLError port.

Rename Saved Search

Table of Contents

Developer Transformation Guide

Developer Transformation Guide

Stored Procedure Example

Stored Procedure Example