Select Procedures

Select the procedure required in the OTD.

To Select Procedures

1. In the Select Procedures and specify ResultSet and Parameter Information dialog box, click Add.

   

2. In the Select Procedures dialog box, enter the name (case-sensitive) of a Procedure or select a table from the drop-down list. Click Search. You can use Wildcard characters.

3. In the resulting Procedure Selection list box, select a Procedure. Click OK.

   

4. In the Select Procedures and specify ResultSet and Parameter Information dialog box, click Edit Parameters to make changes (as needed) to the selected Procedure (see the figure below).

   

5. To restore the data type, click Restore. When you are finished, click OK.

6. Click Edit ResultSets to have the wizard generate the ResultSet(s) for the Oracle OTD.

7. Select the type of Resultset you want to generate and click Add.

   

   The DBWizard provides three different ways to generate the ResultSet nodes of a Stored Procedure. They are the "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 with the by the “By Executing 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.