Creating an Oracle Applications OTD

The Oracle Applications Adapter uses a wizard-based OTD builder to create OTDs based on your Oracle tables. The wizard queries the Oracle tables to determine the hierarchies of the interface tables for a particular module, and creates a corresponding OTD. It also sets up the necessary staging table and the stored validation procedures to be run against the table.

Figure 18 Oracle Applications OTD

When building an OTD, the wizard calls many JDBC APIs (for example, getProcedureColumns()) which in turn queries the database and returns the resultset. While the Adapter itself doesn’t issue the queries directly, it is the Oracle driver that translates the API into multiple queries. In a situation where there is a lot of data in the database, it may take a while to return all the resultsets to the wizard. The performance of the queries is dependent on the execution path which is formulated when a SQL call is prepared. Not having good stats in the data dictionary could produce a long running query.

Oracle recommends doing the following to gather vital stats to improve performance:

1. Set the following in either the init.ora file or spfile (whichever is appropriate for their install):

   _table_lookup_prefetch_size=0

2. Analyze the SYS schema for the system as follows:

   1. Start sqlplus

   2. Connect as sys user

   3. exec dbms_stats.gather_schema_stats(’SYS’);

   Keep in mind that significant changes to the database would affect the data dictionary (like new tables, indexes, etc). You should consider running the analysis regularly.

   Note - Please consult your Database Administrator or Oracle before taking these steps as it may impact other applications.

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

• Select Wizard Type

• Connect To Database

• Select Oracle Applications Module

• Specify the OTD Name

• Review Selections

• Exposed Oracle Applications OTD Nodes