Getting started with stored procedures

Stored procedures in a database can be used in xDI Designer by referencing them in a specific Metadata object. You can then use them in mappings as you would any other datastore, giving them input parameters and retrieving the result.

Create the metadata

Open the Create Metadata wizard. Choose the Stored procedures type, and give your new metadata a meaningful name.

Screenshot of New Metadata wizard with the Stored Procedure option selected

After the wizard closes, the Stored procedures metadata opens in a new tab, and is ready for you to start defining the database schema in which stored procedures are executed, and then the procedures themselves with input and output parameters, and the output set.

Configure the metadata

Define the database schema

Start by setting which database schema you want to use with stored procedures.

  1. Open the properties for the Stored Procedure metadata, and go to the Standard tab.

  2. In the Project Explorer, find the node of the database schema you want to use. Drag and drop this node onto the Schema field of the Stored Procedure metadata properties.

    Screenshot of dragging and dropping a database schema to the store procedure metadata fields

Alternately, you can activate the Schema field, and click the first button on the right to select a process reference, rather than dragging and dropping it.

Define the stored procedure

Continue by referencing a stored procedure from the database.

  1. Right-click the top-level metadata node, and select New > Stored Procedure.

    Screenshot of the menu entry to create a new stored procedure node

  2. In the new procedure node properties, set the Name field to the exact name of an existing stored procedure in the database.

  3. If you want, edit the Description to add your own notes and information.

Define input and output parameters

Next, add input and output parameters.

  1. Right-click the procedure node, and select New > Input Parameter.

  2. Right-click the node again, and select New > Output Parameter.

    Screenshot of the menu entries to create input and output parameter nodes

  3. In the properties for both parameter nodes, set the Name field to the exact name of existing parameters as defined in the stored procedure.

  4. Likewise, set the Type field for both nodes to their respective data types.

Define the output set

Finally, if the stored procedure returns a data set, define its output in a dedicated node.

  1. Right-click on a procedure node, and select New > Output Set.

  2. If you want, set the Name of the output set to a label of your choice.

  3. Right-click on the new output set node and choose New > Output Row.

    Screenshot of the Output Row node

  4. Define individual columns by right-clicking the output row node and choosing New > Output Columns.

    1. Set the Name field of the output column node to the exact name of the column returned by the procedure.

    2. Set the Type of the output column to the column datatype.

Screenshot of output column nodes with their properties

Use stored procedures in mappings

Now that you have a stored procedure defined in Designer metadata, you can use it in mappings like any other datastore. You can map a source datastore to it, and optionally map its fields to a target datastore to retrieve the results.

The root node is the repetition key, and defines how many times the procedure is invoked.

Examples

Example of a mapping with simple inputs and outputs
Figure 1. Input and output parameters
Example of a mapping with an output set
Figure 2. Returning an output set

Notes and limitations

Root nodes in mappings

In a mapping, the root node of a stored procedure has a few limitations:

  1. You cannot map the root node to a field in a target datastore. Only input and output parameters can be mapped.

  2. As the root node is a repetition key, it must be mapped from a source to call the procedure.

Screenshot with explanation of having to map the repetition key properly

Oracle packages

When working with stored procedures in an Oracle database, you can invoke a procedure from inside an Oracle Package.

In the Stored Procedure metadata, edit the procedure Name to add a prefix consisting of the Oracle package name and a dot.

For instance: <package_name>.<procedure_name>

Procedures with no input parameters

Procedures without input parameters are not yet supported. You must map an input parameter to invoke a stored procedure.