Browsing Base Table Metadata

Procedure: How to Search for Oracle Base Tables and Views

To locate a specific Oracle base table using iWay Explorer:

Connect to an Oracle E-Business Suite target, as described in Connecting to Oracle E-Business Suite.
In the left pane, expand the target node.
Expand Applications, Schemas, and then HR.

Note: The schemas under Applications are actual table owners.
Right-click Tables and select Find.

Note: The Tables node provides the capability to interact with base tables and views.

The Find dialog box opens.

Note: Due to the large number of tables available, a search capability is provided. You can search for a particular table by typing the full table name into the Search string field, as shown in the preceding image. Alternatively, you can search for table names beginning with a certain string, for example, typing HR_LOC% generates a list of all tables starting with the string HR_LOC. This search capability is not case sensitive.
Click OK.

The Find dialog box expands and lists the search results, as shown in the following image:
Click the table name.

The HR_LOCATIONS_ALL table is listed under Tables in the left pane.
Expand the HR_LOCATIONS_ALL node.

A list of functions that can be performed on this table is displayed in the left pane.

Important: Oracle recommends that users do not interact directly with application tables. The use of INSERT, DELETE, or UPDATE to modify data in application tables may jeopardize database referential integrity.

You can now generate schemas and create iWay Business Services.

Procedure: How to Use the Additional Table Functions

The additional table functions provide a standard way to store, update, and retrieve data from any database. The GET, INSERT, UPDATE, DELETE, and UPSERT functions operate on a single row at a time. The COUNT, AVERAGE, MIN, MAX, and SUM functions operate on a single table at a time.

To use the additional table functions:

After connecting to a target, expand Applications and then Schemas.
Search for a specific table.

Select a function in the left pane.

All the table functions can be used to create iWay Business Services and to export schemas to create instance XML request documents. You can review the SQL statement in the right pane when you select one of the functions. The following image shows the Insert function selected and the SQL statement displayed in the right pane.

The following table lists and describes the available table functions.

Function	Description
GET	Retrieves one row at a time.
INSERT	Inserts one row into a table.
UPDATE	Updates non-primary keys and operates on a single row at a time. The primary keys sent in the request are used to populate the where clause in the SQL statement. Updating primary keys is logically equivalent to deleting the row by primary key and then inserting a new row with a new ID with the same non-primary key values. This logic fits the DAO, JDO, or EJB frameworks while simply updating the primary keys does not.
DELETE	Deletes one row at a time. The parameters are the primary keys.
UPSERT	Combines the INSERT and UPDATE functions. This function checks if a row already exists. If it does not exist, the request is forwarded to the INSERT function; this is determined by performing a count query with the table’s primary keys as part of the where clause, as shown in the properties in the right pane.
COUNT	The COUNT table function is used to return the number of entries in a table. The query does not accept any input parameters.
AVERAGE	This query executes an avg() function on a table and accepts an input parameter representing the column name. The JDBC API is not used to set the question mark in the statement. This process recreates the statement string replacing the question mark with the value in the input parameter in the request. This is performed before the statement is prepared.
MAX	This query executes a max() function on a table and accepts an input parameter representing the column name.
MIN	This query executes a min() function on a table and accepts an input parameter representing the column name.
SUM	This query executes a sum() function on a table and takes in an input parameter representing the column name.

Tables with no primary keys have only CURSOR and INSERT functions available. Tables that are actually views will have only the CURSOR function available.

In the left pane, select the function you want to use.
In the right pane, move the mouse pointer over Operations and select either Create iWay Business Service or Generate Schema.