Creating an Oracle OTD

The following steps are required to create a new OTD for the Oracle adapter.

• Select Wizard Type

• Connect To Database

• Select Database Objects

• Select Tables/Views/Aliases

• Add Prepared Statement

• Specify the OTD Name

• Review Selections

Select Wizard Type

Select the type of wizard required to build an OTD in the New Object Type Definition Wizard.

To Select the Oracle Database OTD Wizard

1. On the project tree, right click the Project and select New ⇒ Object Type Definition from the shortcut menu. The Select Wizard Type page appears, displaying the available OTD wizards.

2. From the New Object Type Definition Wizard window, select the Oracle Database and click Next. (see the following figure).

   Figure 1–26 OTD Wizard Selection

   
   

Connect To Database

Enter the Oracle database connection information in the Connection Information frame.

To Connect to the Database

1. Specify the applicable connection information for your database including:

   • Host Name - The server where Oracle resides.

   • Port ID- The port number of Oracle.

   • SID - The name of the Oracle instance (equivalent to the database name).

   • User Name - The user name that the Adapter uses to connect to the database.

   • Password - The password used to access the database.

2. Click Next. The Select Database Objects window appears.

Select Database Objects

Select the type of Oracle database objects you want included in the OTD.

To Select Database Objects

1. In the Select Database Objects dialog box (shown below), select Tables/Views for this sample. When selecting Database Objects, you can select any combination of Tables, Views, Procedures, or Prepared Statements you would like to include in the .otd file. Click Next to continue.

   ---

   Note –

   Views are read-only and are for informational purposes only.

   ---

   

2. Click Next to continue. The Select Tables/Views/Aliases window appears (depending on your selection).

Select Tables/Views/Aliases

Select the types of tables or views required in the OTD.

---

Note –

Aliases are not supported in the current release of the Oracle Adapter.

---

To Select Table/Views/Aliases

1. In the Select Tables/Views/Aliases window, click Add (see the figure below).

   

2. In the Add Tables window, select the type of criteria to be used for your search, consisting of table data, view only data, or both. You can include system tables in your search by selecting the checkbox.

3. From the Table/View Name drop down list, select the location of your database table and click Search. You can search for Table/View Names by entering a table name. The use of wildcard characters of ”?’, and ”*’ as part of your Table/View name search allow for greater search capabilities. For example, “AB?CD” or “AB*CD”.

4. Select the table of choice and click OK. The table selected is added to the Selected window (see the figure below).

   

5. On the Selected Tables/Views window, review the table(s) you have selected. To make changes to the selected Table or View, click Change. If you do not wish to make any additional changes, click Next to continue.

6. If you clicked Change on the Selected Tables/Views window, you can select or deselect your table columns on the Table/View Columns window. You can also change the data type for each table by highlighting the data type and selecting a different one from the drop down (see the figure below).

   

7. Click Advanced to change the data type, precision/length, or scale. In general, do not change the precision/length or the scale. Once you have finished your table choices, click OK (see the figure below).

   

8. When using Prepared Statement packages, select Use fully qualified table/view names in the generated Java code.

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.

Add Prepared Statement

A Prepared Statement OTD represents a SQL statement that has been compiled. Fields in the OTD correspond to the input values that users need to provide. Prepared statements can be used to perform insert, update, delete and query operations. A prepared statement uses a question mark (?) as a place holder for input. For example: insert into EMP_TAB (Age, Name, Dept No) values (?, ?, ?)

To execute a prepared statement, set the input parameters and call executeUpdate() and specify the input values if any.

To Add Prepared Statements

---

Note –

When using a Prepared Statement, the ResultsAvailable() method will always return true. Although this method is available, you should not use it with a while loop. Doing so would result in an infinite loop at runtime and will stop all of the system’s CPU. If it is used, it should only be used with the if statement.You can process a resultset by looping through the next() method.

---

1. In the Add Prepared Statements dialog box, click Add.

   Figure 1–27 Prepared Statement

   
   

2. Enter the name of a Prepared Statement and create a SQL statement by clicking in the SQL Statement dialog box or by clicking the Statement Builder button. When you are finished creating the statement, click Save As, which gives the statement the name you just entered. This name appears as a node in the OTD (see the figure below). Click OK.

   Figure 1–28 Prepared SQL Statement

   
   

3. In the Add Prepared Statement dialog box, the name you assigned to the Prepared Statement appears. To edit the parameters, click Edit Parameters. You can change the datatype by clicking in the Type field and selecting a different type from the list.

4. Click Add if you want to add additional parameters to the Statement, or highlight a row and click Remove to remove the parameter (see the following figure). Click OK.

   ---

   Note –

   Once you save the Prepared Statement, make sure that the ResultSet Column Name, in the Prepared Statement parameters, is a valid alpha-numeric string with no special characters (e.g. no brackets).

   ---

   Figure 1–29 Edit the Prepared Statement Parameters

   
   

5. To edit the ResultSet Columns, click Edit ResultSet Columns (see the figure below). Although both the Name and Type are editable, Sun recommends that you do not change the Name because it can cause a loss of integrity between the ResultSet and the Database. Click OK.

   ---

   Note –

   The OTD Wizard fails to create OTDs with complex prepared statements that use the same column name in different tables. This problem is resolved by modifying the SQL statement to use column name aliases.

   ---

   Figure 1–30 ResultSet Columns

   
   

6. In the Add Prepared Statements dialog box, click OK.

Specify the OTD Name

Specify the name that your OTD will display in the Java CAPS IDE.

To Specify the OTD Name

1. Enter a name for the OTD. The OTD contains the selected tables and the package name of the generated classes (see the following figure).

   Figure 1–31 Naming an OTD

   
   

2. Click Next.

Review Selections

Review the selections made for the new OTD.

To Review Your OTD Selections

1. View the summary of the OTD. If you find you have made a mistake, click Back and correct the information.

2. If you are satisfied with the OTD information, click Finish to begin generating the OTD (see the following figure).

   The resulting OTD appears in the Java CAPS IDE.

   Figure 1–32 Database Wizard - Summary