The basic architecture of an Oracle system contains a set of base objects which are held in highly normalized core tables within the Oracle database. A de-normalized view of these base objects is provided in a set of Open Interface Tables (OITs), also maintained in the database. Data is passed from the Open Interface Tables to the core tables under the control of the Concurrent Manager.
In a typical scenario, an operator schedules an import job by means of the Oracle front end, which initiates the following procedure:
Data is passed from the Open Interface Tables to the core tables under the control of Import Jobs scheduled by the Concurrent Manager.
It then invokes the Oracle Concurrent Manager, which:
Validates the data in the Open Interface Table, based on a set of stored SQL procedures.
Inserts the validated rows into the Oracle Applications Database.
There are several limitations to this very basic scheme:
Once data is in the Open Interface Table, it cannot be withdrawn or corrected.
Data failing the validation process may be handled in different ways—some import scripts update the original rows with error codes, while other scripts log errors to a file, requiring user intervention.
Only the default validation rules provided by Oracle are used in the validation process, and may not address specific customer requirements.
There is no easy way to insert batches of data as a transactional unit—for example, where all inserts from a batch must succeed (if any fail, then all must fail).