Oracle Open Interface

What is Oracle open interfaces?

Open interfaces used to integrate Oracle Applications with the non-oracle systems.
such as
1. Import data from your custom applications or legacy systems.
2. Migrate the old custom application data to the Oracle applications.
3. Import data from the banks, partners, suppliers, customers etc.


What is conversion?

Conversion is a one time Interface to migrate data from legacy application to the Oracle


Inbound Interface
The Interface which transforms and loads the data into the Oracle Applications system is called inbound interface.


Outbound Interface
The Interface which extracts the data from the oracle Application system for the purpose of loading into the external system is called outbound interface.


Common approach to interface is

1. Data would be extracted from the source machine and loaded into the custom table (stagging table) in Oracle apps database. Depending upon the complexity of the data Stagging table can be avoided.
2. The extracted data is then transformed into a form which oracle can recognize and pushed into interface tables.
3. Final step is load the data from interface table to base table of Oracle applications.

This whole process is called extract load and transformation (ETL).


Oracle provides Interface tables for data intensive applications like Invoice, receipts, journals etc and PL/SQL APIs for master data interfaces like people, items, suppliers etc.


Traditional approach used sql Loader to load the data and pl/sql program to validate and extract data.
Modern approach uses BPEL, ESB for online data interfaces and Oracle Data Integrator for data intensive batch processing.


Common Interface technologies
1. XML Gateway
2. SQL*Loader
3. DB Link
4. Java Concurrent Program
5. BPEL or ESB
6. Oracle Data Integrator or ODI
7. PL/SQL package for reading file or XML data
8. Oracle Advanced Queuing


Some Useful Links

1. Oracle Integration Repository - List of Oracle open interfaces
2. Technical reference Manual - Column level details of Interface tables, base tables and APIs
3. Technical reference Manual for different versions.





Article By:
Prasanna Jayaraman

Please post your Ideas and Comments