Using a stored procedure

A database transport map can be defined to invoke a stored procedure with IN, OUT, and INOUT parameters and also process data from multiple result sets.
When the stored procedure invocation is complete, the data from the OUT or INOUT parameters can be written to output variables.
If the stored procedure returns result sets, this data can also be written to output variables.

Assumptions

You are familiar with configuring and invoking a stored procedure in the database server.

Defining the Stored Procedure transport map

1. Using the Enterprise -> Transport Map tab, select New to define a new transport map.
   
2. Select a previously defined database transport in the Transport Name parameter.
   
3. When the metadata is returned from the gateway, and the Workbench populates the Schema parameter, select the database schema you want to use.
   
4. In the Action parameter, select Stored Procedure.
   
5. In the Procedure parameter, select the stored procedure you want to invoke.
   For example:
   
   
6. The Workbench queries the stored procedure metadata from the database and populates the StoredProcedure tab with parameter information.
   
7. The From/To Enterprise title indicates the direction of the data flows both ways (bidirectional). In the case of a stored procedure, the transaction would start with data flowing to the stored procedure and would finish with data coming from the stored procedure.
   
8. The Allow in Store and Forward check box is used to specify whether to invoke the stored procedure when the transport is in the 'Store and Forward' state. If the box is unchecked a transport map with a stored procedure with INOUT or OUT parameters will not be added to the Store and Forward queue. This is because at some future point in time, when the transport is able to connect to the database server and execute the stored procedure, it will not be able to deliver data from the INOUT and OUT parameters of the stored procedure to a trigger action. Selecting the Allow in Store and Forward  checkbox allows the transport map to be written to a Store and Forward queue with the understanding that when the procedure is eventually executed, the data sent back to the map in the INOUT and OUT parameters will be dropped (not processed).
   
9. The Output tab becomes available when a stored procedure with OUT or INOUT parameters is specified.
   
   
10. The Input tab will consist of a map variables whose values are mapped to IN or INOUT parameters of the stored procedure. The Output tab will consist of map variables that are the destination of data returned from the OUT or INOUT parameters of the stored procedure.
    
11. To map a value to an IN or INOUT parameter, create a map variable using the Input tab.
    
12. If the stored procedure is to return a value to an OUT parameter, create the map variable using the Output tab.
    
13. When adding a map variable that will be used as an INOUT parameter by the stored procedure, you must create the variable on both the Input and Output tab. The variable names must exactly match; otherwise, the StoredProcedure tab will not recognize the variable as an INOUT parameter.
    
    
14. Now associate the map variables with the stored procedure parameters. If you need help with this task, go to Associating map variables with table columns.
    
15. You can have the workbench automatically create map variables and associate them with stored procedure parameters by selecting the Map Parms button. If the variable is mapped to an INOUT parameter, it will be added to both the Input and Output tabs.

Configuring result sets

The Result Sets parameter is optional. It is used to specify the number of result sets the stored procedure is expected to return.

• If the Result Sets parameter is left blank, it indicates that you do not want to process result set information that may be returned from the stored procedure.
• If the Result Sets parameter is set to a value, the Workbench will create a set of tabs that corresponds to the number entered as shown in the example below.
  • These tabs can be used to specify column metadata information for the result set.
  • The order of the tabs represents the order in which the map is expecting  the result sets to be returned by the stored procedure.
    
    

If you are working with an Oracle stored procedure that returns result sets, each result set is returned as an OUT parameter of type REF CURSOR.
This is indicated by the Result Sets parameter that is not editable. It will be populated with the number of OUT parameters that contain result set information. 

The following example shows a transport map configured to invoke an Oracle stored procedure.
Note the Result Sets parameter is not editable and the DB Type column of the procedure parameters that return a result set is "REF CURSOR".
The number of result set tabs corresponds to the number of "REF CURSOR" parameters.

Specifying Result Set tab metadata

Navigate to a Result Set tab to specify metadata information about the result set.

• The Max Rows parameter is used to specify the maximum number of rows that will be processed from this result set.
• The Name parameter can be used to specify a prefix for output variables that are created to map content from this result set. It is populated with a default value.
  
  

You can specify stored procedure metadata information by adding individual column information or by using the table lookup helper which is accessed by selecting the From Table... button. 

Specifying result set column data individually

Select the Add button and a row will be added to the Result Set tab as shown in the example below:

Each row in the Result Set tab has the following columns:

Parameter	Description
Column	The column name of the result set item that will be mapped.
Logical	The output variable data type that the column data will be converted to.
Variable	The output variable name that this result set column will be mapped to.
Default Value	A default value to be used when the column data is NULL.

You can use the Remove button to remove a row from the Result Set tab. 

Specifying result set column data based on a database table

Select the From Table... button to specify a table for the result set metadata.

A table selection panel is displayed, as shown in the example below.

Select the Schema and the Table.

The column information will be populated from the table.

• The columns show the name of the result set column (Name), the database datatype (DB Type)  and the logical output variable datatype (Logical).
• You can multi-select the rows you want to add to the Result Set tab, and then select the Add Selected button.
• If you want to add all columns to the Result Set tab, select the Add All button.
  
  

After selecting Add All or Add Selected, the popup panel is dismissed and the Result Set tab will be populated with table metadata as shown in the example below.

You can modify the contents of the panel by adding additional rows or removing existing ones. You can also edit the Column name or the Logical data type if needed.

This process can be used to specify result set metadata for each of the tabs.

Mapping input and output variables

Select the Map Parms button to have the workbench automatically create stored procedure and result set input and output variable data.

• The Input tab will contain variables for all IN and INOUT parameters.
• The Output tab will contain variables for INOUT and result set tab data. All variables that get mapped from result set data will have a result set name prefix.
  For example the output variable created for the 'site_num' result set parameter will be called 'rs1_site_num'.
  
  

When you define a trigger that uses a Transaction action that references this Stored Procedure transport map, you associate the transport map's Output tab variables variables with the trigger's Transaction action Output tab.

Validating and saving the transport map

1. Select Validate.
   A window appears and displays a representation of the SQL statement that will be invoked when the transport map is processed.
   
   
2. To close the window, select OK.
   
3. Select Save to save the transport map definition.

Using the Store Procedure in a trigger's Transaction action

The Stored Procedure transport map can be referenced in a trigger's Transaction action. The Input and Output tabs of the Transaction action will display input and output variables that were configured in the Stored Procedure transport map.

If the Stored Procedure transport map is configured to process result sets you will see output variables that represent the number of rows that are returned in each result set.
In the example below the output variables  'rs1_rows_out'  and 'rs2_rows_out' will contain the number of rows returned by each result set after the action executes successfully.