When you call a stored procedure from an SQL transformation, each field that the call statement references identifies an input or output port. When you import a stored procedure, the Developer tool generates the stored procedure call statement. Otherwise, you need to manually configure the call statement.
Enclose the port names in question marks. The port names do not have to match the parameter names in the stored procedure. The output ports must be in the same order as parameters in a SELECT query.
You can use a stored procedure that contains INOUT parameters. The SQL transformation identifies INOUT parameters by the input port name. The output port has the prefix
output_
. The Data Integration service binds the input port and output port to the same parameter.
You can configure an SQL transformation to return a result set. When the stored procedure returns a result set, the Developer tool cannot create output ports for the columns in the result set. When you import the stored procedure, you must manually enter the ports and configure the stored procedure call.