Data Transformation Engine Database Interface Designer Reference Guide

Chapter 8 - Using Stored Procedures Stored Procedures with Object Type Parameters
Database Interface Designer Reference Guide
128
There are two ways to call stored procedures in output cards:
♦ In the output card dialog box in the Map Designer, specify
-PROC
procedure_name in the PUT > Target > Command setting. For more
information, refer to Using a Database as a Target.
or
♦ When overriding an output card using database adapter commands on the
command line, use the
-PROC adapter command and specify the stored
procedure name. For more information, refer to the Resource Adapters
Reference Guide.
The values for each parameter are passed to the stored procedure with the stored
procedure being called for each row of data.
Note The types of the parameters may be IN, OUT, or IN/OUT. However, there is no
mechanism to return values from output parameters. Any values passed to an OUT
parameter will be ignored.
Stored Procedures with Object Type Parameters
The Oracle adapter provides full support for Oracle object types to be used as
parameters to stored procedures or functions. Mapping to a stored procedure call
in an output card is no different than mapping to a table; object types as
parameters are fully supported.
Similarly, invoking stored procedures with object type parameters using the 'call'
syntax is possible. For output parameters, there is no special syntax required.
Using a question mark character (?) will result in the entire object being returned
from the stored procedure call. However, for input parameters, a special syntax is
required to specify the objects. The syntax rules are as follows:
♦ The object must be contained within square brackets. An example of this is:
"[…….]"
♦ Each element of the type is separated from other elements of the type by the
pipe (|) delimiter character.
♦ Spaces are not allowed unless they are contained within the data itself.