Select Procedures

Select the type of stored procedures required in your OTD.

To Select Stored Procedures

1. On the Select Procedures and specify Resultset and Parameter Information window, click Add.
   
2. On the Select Procedures window, enter the name of a Procedure or select a schema from the drop down list. Click Search. Wildcard characters can also be used.
3. In the resulting Procedure Selection list box, select a Procedure. Click OK.
   
4. On the Select Procedures and specify Resultset and Parameter Information window click Edit Parameters to make any changes to the selected Procedure. See the following figure.
   
5. To restore the data type, click Restore. When finished, click OK.
6. To select how you would like the OTD to generate the nodes for the Resultset click Edit Resultsets.
7. Click Add to add the type of Resultset node you would like to generate.
   

   The DBWizard provides three different ways to generate the ResultSet nodes of a Stored Procedure. They are "By Executing", "Manually", and "With Assistance" modes.

   By Executing Mode “By Executing” mode executes the specified Stored Procedure with default values to generate the ResultSet(s). Depending on the business logic of the Stored Procedure, zero or more ResultSets can be returned from the execution. In the case that there are multiple ResultSets and "By Executing" mode does not return all ResultSets, one should use the other modes to generate the ResultSet nodes. With Assistance Mode "With Assistance" mode allows users to specify a query and execute it to generate the ResultSet node. To facilitate this operation, the DBWizard tries to retrieve the content of the specified Stored Procedure and display it. However, content retrieval is not supported by all types of Stored Procedures. We can roughly classify Stored Procedures into two types: SQL and external. SQL Stored Procedures are created using CREATE PROCEDURE SQL statements while external Stored Procedures are created using host languages (e.g. Java). Since external Stored Procedures do not store their execution plans in the database, content retrieval is impossible. When using "Assist" mode, highlight the execute statement up to and including the table name(s) before executing the query. Manually Mode "Manually" mode is the most flexible way to generate the result set nodes. It allows users to specify the node name, original column name and data type manually. One drawback of this method is that users need to know the original column names and data types. This is not always possible. For example, the column name of 3*C in this query. SELECT A, B, 3*C FROM table T is generated by the database. In this case, "With Assistance" mode is a better choice. If you modify the ResultSet generated by the “Execute” mode of the Database Wizard you need to make sure the indexes match the Stored Procedure. This assures your ResultSet indexes are preserved.

8. On the Select Procedures and specify Resultset and Parameter Information window click Next to continue.

   Note - Not all drivers and databases support stored procedures or stored procedures with ResultSets. You may contact the driver’s vendor for more information.