Oracle Openworld Directory Index

Information Integration in a Heterogeneous Environment
Free Web space and hosting from
Search the Web

Information Integration in a Heterogeneous Environment

Vira Goorah, Oracle Corporation


Companies are realizing huge payoffs by consolidating data into a single database. However, many find it a struggle to consolidate all of their data. Their environment, rather than being carefully designed for consolidation, has evolved over many years. Data resides in multiple vendors' databases and is sent between applications using a variety of messaging technologies. The challenge is to move toward consolidation yet still support the many different databases and messaging technologies required by legacy systems.

This paper discusses the variety of ways companies using Oracle9i can share information in a heterogeneous environment. It provides Transparent Gateways to common data stores and Generic Connectivity for data stores supporting OLEDB or ODBC. Transparent Gateways and Generic Connectivity make the non-Oracle system look like a remote Oracle system, simplifying development by providing both location and platform transparency. In addition, Oracle9i provides a messaging gateway for directly exchanging messages with common message queuing systems. Oracle9i also supports a variety of standard open interfaces, including XML over HTTP/SMTP, JMS, JDBC, ODBC, and the Oracle C/C++ Call Interface, providing the greatest flexibility for integrating Oracle9i in any environment.

Information integration problem

As companies grow and expand they are faced with the challenge of effectively and economically operating in a distributed decentralized environment. As the company grows so does the number of applications that it is running, each with its own separate data store. This data is just bits and bytes on a file system - and only a database can turn the bits and bytes of data into business information. To thrive in today’s dynamic and fast paced business environment, information sharing and integration is a must across applications and departments.

Let us use Estee Lauder as an example of a company operating in a heterogeneous environment. Their warehousing and distribution system uses DB2 and Informix. They have another application that uses Oracle and needs to access the data in DB2 and Informix. Although the best solution for their problem would be for all the various applications to consolidate their data in a single database, this is, if at all possible, a time consuming and costly process. It would take most companies a few years to achieve the goal. Credit Derivatives House in UK decided to do just that, move to Oracle. However due to time constraints they could not simply stop using Sybase and move to Oracle. Instead they have implemented Oracle and are gradually migrating from Sybase to Oracle. However, in the interim, all aspects of their business still need to function and the business has to run as usual. This means that all the various applications will need to interoperate. Even if companies are able to consolidate all their internal information, they still require integration solutions for sharing information with their partners and customers. Oracle, recognizes this need and provides solutions so that companies can function in such an environment.

Oracle9i is the information management solution for such companies.

Oracle9i Information integration Solutions

Oracle9i is the leading database in the industry that allows organizations to efficiently store, manage, integrate and use business information. It has been designed to provide the most complete and low cost solution. Like its predecessors, Oracle9i offers a robust, reliable, secure and scaleable environment, enabling organizations to develop and deploy business solutions quickly.

More importantly, in the context of this paper, Oracle9i includes the ability to integrate and consolidate all information for an organization from a central point. By enabling an integrated and complete solution for all business information, Oracle9i maximizes the efficiency and minimizes the cost of information sharing. More importantly, integration and consolidation of information in this manner allows an organization to easily and quickly take advantage of the synergies inherent in business information.

Oracle offers several solutions for sharing information, within, as well as, outside an enterprise. There are two scenarios of information integration: synchronous and asynchronous. Synchronous access uses Oracle9i Distributed SQL features to consolidate data on the fly, masking the location of data from the application or user by making it appear as a local table. Asynchronous integration uses messaging and replication technologies to move data from a remote to database to a local database, where applications can directly access the data. Oracle9i Release 2 introduces a new asynchronous information integration feature, called Oracle9i Streams. Oracle9i Streams enables the propagation of data, transactions and events in a data stream either within a database, or from one database to another. The stream routes published information to subscribed destinations. The result is a new feature that provides greater functionality and flexibility than traditional solutions for capturing and managing events, and sharing the events with other databases and applications.

Oracle offers several technologies for integrating information in a heterogeneous environment. These features extend Oracle’s capabilities to work with non-Oracle data sources, non-Oracle message queuing systems, and non-SQL applications, ensuring interoperabilility with other vendor’s products and technologies. This paper discusses the following technologies:

·         Transparent Gateways: - These offer transparent connectivity to other non-Oracle database vendors such as DB2, Microsoft SQL Server, Sybase, Informix and Teradata to name a few.

·         Generic Connectivity: - This is a feature that enables integration with any data source using industry standards, ODBC and OLE DB.

·         Messaging Gateway: - The Messaging Gateway enables communication between Oracle and other non-Oracle message queuing systems.

·         Open System Interfaces: - Oracle offers a number of open interfaces such as OCI, JDBC and ODBC to enable customers to use third party applications or write their own client applications to access the Oracle9i database.

Each of these integration solutions are described in detail in the following sections.

Generic Connectivity and Transparent Gateways

Generic Connectivity and Transparent Gateways enable Oracle clients to access non-Oracle data stores. They translate third-party SQL dialects, data dictionaries, and data types into Oracle formats, thus making the non-Oracle data store appear as a remote Oracle database. These technologies enable companies to seamlessly integrate the different systems and provide a consolidated view of the company as a whole.

Generic Connectivity and Oracle Transparent Gateways can be used for synchronous access, using distributed SQL, and for asynchronous access, using Oracle9i Streams. Introducing a Transparent Gateway into an Oracle9i Stream enables replication of data from an Oracle database to a non-Oracle database.

Both Estee Lauder and Credit Derivatives House solved their problem using Transparent Gateways. Estee Lauder now has real-time access to the data in DB2 using Transparent Gateway for DB2 and to Informix using Transparent Gateway for Informix. Credit Derivatives is gradually moving all their data over to Oracle. Although their front end application is still using Sybase, they are moving the data on a daily basis to Oracle using the Transparent Gateway for Sybase.

Whether used with distributed SQL, or Oracle Streams Generic Connectivity and Transparent Gateways have to meet some basic requirements. Let us examine the requirements for connecting to non-Oracle systems and what these two solutions offer.

Heterogeneous transparency

Both Generic Connectivity and Oracle Transparent Gateways provide the ability to transparently access data in non-Oracle systems from an Oracle environment. As with an Oracle distributed database environment, location transparency can be extended to objects residing in non-Oracle systems as well. Therefore users can create synonyms for the objects in the non-Oracle system and refer to them without having to specify their physical location. This transparency eliminates the need for application developers to customize their applications to access data from different non-Oracle systems, thus decreasing development efforts and increasing the mobility of the application. Instead of requiring applications to interoperate with non-Oracle systems using their native interfaces (which can result in intensive application-side processing), applications can be built upon a consistent Oracle interface for both Oracle and non-Oracle systems.

Consider the scenario where a customer uses Oracle Transparent Gateways to access heterogeneously stored data with the plan to migrate the heterogeneous data to an Oracle system. If a database application is developed to interoperate with both an Oracle and a non-Oracle system using their native interfaces, once the migration to a homogeneous Oracle environment is complete, the database application would need to be altered to operate in that environment.

connecting disparate systems

Although the user interfaces for different non-Oracle systems based on SQL standards may appear to work identically, there may be subtle (and not so subtle) differences between these non-Oracle systems. These differences may prevent the disparate systems from interoperating effectively.

For smooth interoperability between disparate systems, SQL translations, data dictionary translations and data type translations are required, even if the non-Oracle systems are based on SQL standards. Both Generic Connectivity and Oracle Transparent Gateways have the ability to translate one system's dialect to another.

SQL Translations

Even though a relational data store may be based on SQL standards, there may be subtle differences between manufacturers in the implementation. For example, if you wanted the following result set to be in uppercase letters, you would execute the following SQL statement in an Oracle environment:


In a non-Oracle environment, however, you might execute the following SQL statement to retrieve the same results:


Both Generic Connectivity and Transparent Gateways transparently translate the dialect of the foreign system to that of the local system (where the transaction originated), eliminating the need for the user to utilize more than one system’s dialect.

Data Dictionary Translations

Metadata, or information about a database environment, is extremely useful to DBAs managing a database environment. Different manufacturers have their own methods of storing this data in a data dictionary and displaying this data. Oracle’s heterogeneous solutions provide a mechanism to allow a query of the metadata at a remote disparate system to be displayed in the format of the local system.

For example, a DBA might issue the following SQL statement to view all tables in an Oracle database:

SELECT * FROM sys.dba_objects WHERE object_type = ‘TABLE’;

In a non-Oracle environment, the following SELECT statement might yield the same results:

SELECT * FROM catalog_objects

WHERE object_name LIKE ‘%EP%’

AND object_type = ‘TABLE’;

Just as for SQL translations, a data dictionary query in the dialect of the local database are translated to that of the target remote database. This translation involves rewriting the local SELECT statement into a query that will produce the same results by querying the remote system.

Datatype Translations

The final area that affects the interoperation of disparate systems involves translating one manufacturer’s datatype to another manufacturer’s. For example, a DB2 PACKED DECIMAL datatype is transparently handled, translating to an Oracle NUMBER datatype.

When a DESCRIBE statement is issued for a particular remote object, it describes with the datatypes of the local database.

Heterogeneous Connectivity Process Architecture

Gateway technology is composed of two parts: a component that has the generic technology to connect to a non-Oracle system, which is common to all the non-Oracle systems, called Heterogeneous Services (HS) and a component that is target specific, called an Agent. Heterogeneous Services in conjunction with the Agent enables transparent access to non-Oracle systems from an Oracle environment.

Figure 1. below describes the architecture for Heterogeneous Connectivity.


Figure 1. Heterogeneous Connectivity Architecture

Heterogeneous Services(HS)

Heterogeneous Services provides the generic technology for connecting to non-Oracle systems and is the processing power of both solutions. Generic Connectivity and Oracle Transparent Gateways are based on Heterogeneous Services. As an integrated component of the database, Heterogeneous Services can exploit features of the database, such as the powerful SQL parsing and distributed optimization capabilities.

Heterogeneous Services extends the Oracle SQL engine to recognize the SQL and procedural capabilities of the remote non-Oracle system and the mappings required to obtain necessary data dictionary information. Heterogeneous Services provides two types of translations: the ability to translate Oracle SQL into the proper dialect of the non-Oracle system, as well as, data dictionary translations, which display the metadata of the non-Oracle system in the local format. For situations where no translations are available, native SQL can be issued to the non-Oracle system using the pass-through feature of Heterogeneous Services.

Heterogeneous Services also makes it possible for non-Oracle systems to be integrated into Oracle database server transactions and sessions. When a non-Oracle system is accessed for the first time over a database link within an Oracle user session, an authenticated session in the non-Oracle system is transparently set up. At the end of the Oracle user session, the authenticated session in the non-Oracle database system is transparently closed. Additionally, one or more non-Oracle systems can participate in an Oracle distributed transaction. When an application commits a transaction, Oracle’s two-phase commit protocol accesses the non-Oracle database system to coordinate transparently the distributed transaction. Even in those cases where the non-Oracle system does not support all aspects of Oracle two-phase commit protocol, Oracle can (with some limitations) support distributed transactions with the non-Oracle system.

The pass-through SQL feature of HS provides the facility to issue native SQL directly against a non-Oracle system without being interpreted. This flexibility enables you to execute functions or procedures on the non-Oracle system that are not supported by the Oracle Transparent Gateway.


An agent is the process through which an Oracle server connects to a non-Oracle system. The agent process consists of two components. These are agent generic code and a non-Oracle system-specific driver. An agent exists primarily to isolate the Oracle database server from third-party code. In order for a process to access the non-Oracle system, the non-Oracle system client libraries have to be linked into it. In the absence of the agent process, these libraries would have to be directly linked into the Oracle database and problems in this code could cause the Oracle server to go down. Having an agent process isolates the Oracle server from any problems in third-party code so that even if a fatal error takes place, only the agent process will end.

An agent can reside in the following places:

  • On the same machine as the non-Oracle system

  • On the same machine as the Oracle server

  • On a machine different from either of these two

    The capabilities, SQL mappings, datatype conversions, and interface to the remote non-Oracle system are contained in the Agent. The agent interacts with Heterogeneous Services to provide the transparent connectivity between Oracle and non-Oracle systems. There are two types of agents: HS agents and Transparent Gateway agents.

    There are two types of HS agents: HS ODBC which uses an OBDC driver to talk with the non-Oracle system and HS OLEDB which uses an OLEDB driver. HS agents are part of the Oracle9i, so they are installed by default with these products. Generic Connectivity use these agents to connect to the non-Oracle systems.

    Transparent Gateway agents are part of Oracle9i however they are licensed separately. Unlike the HS agents which have to be on the same machine as the Oracle database, the Transparent Gateway agents can be installed on any machine. They can be on the same machine as the Oracle database or on the same machine as the non-Oracle system or on a third machine as a stand alone. Each configuration has its advantages and disadvantages. The issues to consider when determining where to install these agents are network traffic, operating system platform availability, hardware resources and storage. Oracle Transparent Gateways use these agents to connect to the non-Oracle systems.

    Generic Connectivity

    Generic Connectivity is a feature of Oracle9i. It is a generic solution that uses an ODBC or OLEDB driver to access any ODBC or OLEDB compliant non-Oracle system. It addresses the needs of data access to many data stores for which Oracle does not have a gateway solution. This feature enables transparent connectivity using industry standards such as ODBC and OLEDB. Generic connectivity makes it possible to access low-end data stores such as Foxpro, Access, dBase and non-relational targets like Excel.

    Oracle Transparent Gateways

    In contrast to Generic Connectivity which is a generic solution, Oracle Transparent Gateways are tailored solutions, specifically coded for the non-Oracle system. They provide an optimized solution, with more functionality and better performance than Generic Connectivity. Generic Connectivity relies on industry standards, whereas Oracle Transparent Gateways accesses the non-Oracle systems using their native interface. The Transparent Gateways are also end-to-end certified. Oracle has Transparent Gateways to many sources, Sybase, DB2, Informix, Microsoft SQL Server, Ingres, Teradata, to name a few.

    Oracle messaging gateway

    Messaging Gateway, is an Oracle9i database feature. Its primary function is to provide integration between Oracle9i Streams and other enterprise messaging vendors. It enables applications that utilize Streams’s messaging capabilities to communicate with applications that are based on non-Oracle messaging systems such as MQ Series.

    There are many legacy applications that are based on non-Oracle messaging systems. Although the best solution would be to convert these applications to use Streams, this is not always feasible. However integration of these applications is still highly desirable. Oracle recognizes this requirement and provides the Messaging gateway which enables asynchronous, seamless integration between Oracle and other messaging systems.

    Messaging Gateway extends Streams’s message propagation to non-Oracle messaging systems. Message propagation across different messaging systems involves handling different operation APIs and message format conversions. The Messaging Gateway performs the message transformations and propagates the messages to the non-Oracle messaging systems. It guarantees automatic message propagation between Streams queues and MQ Series in an Oracle database.


    Figure 2. Messaging Gateway Architecture

    Figure 2. above describes the Messaging Gateway architecture. The Messaging Gateway has two main components: an administration package and a gateway agent.

    The administration package is named DBMS_MGWADM and is used for configuration and management of the gateway as well as for monitoring the propagation process.

    The agent consists of a propagation engine and a set of drivers that communicate with the non-Oracle messaging systems. There is one driver for each non-Oracle messaging system, however there can be many links from the driver to many queues of that message system. Communication between the agent and the different messaging systems is client/server based. The agent communicates with Streams using a thick JDBC driver.

    The agent schedules and processes propagation jobs. It runs outside the database as an external C routine. The external C routine then invokes a JVM where the actual agent code (Java) runs. The agent routine is registered in the Oracle database job queue.

    The Oracle Messaging Gateway includes the following features:

  • Guaranteed and Automatic Message Propagation

    Messaging Gateway guarantees automatic propagation of messages between Streams queues and non-Oracle system queues. Once scheduled, the gateway agent automatically picks up the messages intended for MQ Series queues from Streams queues and transactionally propagates them to MQ Series queues. Similarly, it propagates messages from MQ Series queues intended for Streams queues.

  • Automatic Message Transformation

    Messages are converted from MQ Series datatypes to Oracle datatypes and vice versa. The Messaging gateway provides automatic message conversion for the types that can be directly mapped from MQ Series datatypes to Streams datatypes. Streams also supports the entire extensible type system of the Oracle database. For these types, the Messaging Gateway supports user-defined transformations that can be used to define mappings from Oracle's object type to an MQ Series message type. These user-defined transformations are automatically applied during propagation.

  • Internet enables non-Oracle queues

    Messages can be enqueued and dequeued into Streams over the Internet. Since the Messaging gateway is an extension of Streams it automatically extends access to the non-Oracle queues via the Internet. Messages destined for the non-Oracle queues can be enqueued securely over the Internet into a Streams queue and automatically propagated to the non-Oracle queue.

    Similarly, messages from a non-Oracle queue can be automatically propagated to the Streams queue, and then sent to applications over the Internet.

  • Unique Message Management Benefits

    In many situations, for example, when sending financial information, the messaging operations require not only guaranteed delivery but also reliable auditing. With the Messaging gateway, messaging operations are automatically audited. An entire message history can be queried using a SQL view. This history can also be used for extracting tracking information and business intelligence.

    The Messaging Gateway has client authentication of the Oracle and the non-Oracle messaging system.

    Open interfaces

    A large percentage of applications can be integrated using the above solutions, however there are many applications that still cannot be integrated using these solutions, for example, applications written in JAVA or Visual Basics that need to access the Oracle database. These types of applications need some other integration solution. For such cases Oracle offers several standard open interfaces. Open interfaces are also useful for situations where the performance level of the packaged solutions is not adequate.

    Open interfaces are provided so that application developers can write their own custom programs to directly access the Oracle database using industry standards. This is a critical feature for many customers because it allows them to write programs that are specifically designed and tuned for their needs and environment. Open interfaces can be used to either directly access data in an Oracle database table, or to exchange data with Oracle Streams. Enqueuing data into, or dequeuing data from a Stream enables developers to loosely couple their applications with other applications and databases, improving availability and increasing maintainability.

    Oracle offers a number of open interfaces. This paper will address the following:

  • Extensible Markup Language

  • Java Messaging Service

  • Oracle Call Interface

  • Industry standard database access

  • Open Database Connectivity

  • Java Database Connectivity

    Extensible Markup Language (XML)

    Extensible Markup Language (XML) is a tag-based markup language that lets developers create their own tags to describe data that's exchanged between applications and systems over the Internet. XML is being widely adopted as the common language of information exchange between companies. One reason for its popularity is its ease of use: XML documents and XML-based messages can be sent easily over the Internet using common protocols such as HTTP or FTP.

    Oracle9i offers a new XML-based Application Programming Interface (API) to Oracle9i Streams that enables easy exchange of business events between businesses over the Internet. It allows organizations to communicate securely over the Internet, allowing external systems, such as customers and partners to be easily integrated. In order to enable the exchange of business events to be performed across the Internet, Oracle9i provides a new communication protocol. This protocol is referred to as Internet Document Access Protocol (iDAP). iDAP, is an extension to Simple Object Access Protocol (SOAP) for messaging. It is an XML based lightweight protocol for the exchange of information in a decentralized, distributed environment.

    Figure 3 below describes the architecture for exchanging information over the Internet using an XML based API

    Figure 3. Messaging using XML over HTTP Architecture

    Information can be exchanged over the Internet using XML, in three simple steps:

    1) A client application, for a simple javascript, generates an XML message, conforming to iDAP.

    2) This message is passed to a Streams servlet, where the XML message is interpreted

    3) The Streams servlet connects to the Oracle database and performs the operations specified in the XML message

    Java Messaging Service (JMS)

    Java Messaging Service (JMS) is a messaging standard developed by Sun Microsystems along with Oracle, IBM and other vendors. It defines a set of interfaces for JMS applications and specifies the behavior implemented by JMS providers. JMS provides a standards based API to enable asynchronous exchange of business events within the enterprise as well as with customers and partners. JMS facilitates reliable communication between loosely coupled components in a distributed environment, significantly simplifying the effort required for enterprise integration. The combination of Java technology with enterprise messaging enables development of portable applications.

    Oracle Java Messaging Service is a Java API for Oracle9i Streams, based on the JMS standard. Figure 4 below describes the Java messaging architecture. Multiple client applications can send and receive messages of any type through a central JMS provider (Oracle Streams). The JMS client consists of the Java application as well as a messaging client runtime library that implements the JMS interface and communicates with Oracle Streams.



    Figure 4. Java Messaging Architecture

    Oracle JMS supports the standard JMS interfaces and has extensions to support other Streams features that are not a part of the standard. It can be used to enqueue and dequeue messages in the queue available with Oracle Streams. Oracle JMS includes the standard JMS features:

  • Point-to-point communication using queues

  • Publish-subscribe communication using topics

  • Synchronous and asynchronous message exchange

  • Subject-based routing

    Oracle Streams also provides extensions to the standard JMS features:

  • Point-to-multipoint communication using a recipient list for specifying the applications to receive the messages

  • Administrative API to create the queue tables, queues and subjects

  • Automatic propagation of messages between queues on different databases, enabling the application to define remote subscribers

  • Transacted session support, allowing both JMS and SQL operations in one transaction

  • Message retention after message is consumed

  • Exception handling

  • Delay specification before a message is visible

    Oracle Call Interface (OCI)

    The Oracle Call Interface (OCI) is an API that is available as part of the Oracle server. It enables application developers to write custom clients to directly access Oracle using C, eliminating the need to embed SQL or PL/SQL within the C program. It is a third-generation language call level interface that allows applications to interact with the Oracle server using C procedures or function calls. OCI can directly access data in Oracle tables, or can enqueue and dequeue data into or out of Oracle9i Streams.

    OCI provides a library of standard database access and retrieval functions in the form of a dynamic runtime library, which can be linked in by the calling applications. Figure 5. below shows the development process using OCI. OCI gives the programs the capability to perform a full range of database operations, including data processing and object manipulation.

    Figure 5. The OCI Development Process

    The function calls provided by OCI offer the application developer the greatest degree of control over program execution and SQL statement processing. OCI, therefore, provides a highly tunable interface to the database thus enabling the application to service increasing number of users and requests without additional hardware investments.

    OCI provides significant advantages over other methods of accessing an Oracle database:

  • More fine-grained control over all phases of the business logic execution

  • High degree of control over the program execution

  • Use of 3GL programming techniques and application development tools

  • Describe functionality to expose layers of the server metadata

  • Support of dynamic SQL

    Industry Standard Database access

    An industry standard to access data in various sources is a must. Let’s consider a company that has several sales channels. Each channel using its own sales application. If this company wanted to get sales information at the company level, analyze sales trends etc., it would have to write an application that would interact with all the different data sources. Writing such an application could be a nightmare, since this would mean that the application would have to, have code that talks to all the different databases using each one’s proprietary language. Adding to the problem if the company added new sales channels the application would have to be modified to get data from those sources. This would be a daunting, never ending development effort.

    In order to avoid such problems, there are industry standard specifications for a database API that have been established. Applications use these standards so that they can all interoperate. These industry standards are very useful for organizations since it enables them to easily connect all their corporate data in a heterogeneous environment. It enables uniform seamless connectivity and interoperability in a heterogeneous environment, providing a complete cross-platform solution.

    Now let us take a look at our above example. If the different sales applications were all compliant to an industry standard they could all share data and the organization would still have the flexibility to add new applications.

    There are two industry standards that this paper covers:

  • Open Database Connectivity (ODBC)

  • Java Database Connectivity (JDBC)

  • Both ODBC and JDBC are used to directly access data in an Oracle database. However, they can also be used to indirectly place data into Oracle9i Streams by performing DML on a local database, and then letting Stream’s implicit capture capability convert that DML into an event in the Stream. Likewise, these ODBC and JDBC can be used to extract data from a Stream by using Stream’s implicit apply engine to apply the event as DML in a local database, where the data is then directly queried. This allows users of ODBC and JDBC access other applications and remote databases in a loosely coupled manner.

    Open Database Connectivity (ODBC)

    Open Database Connectivity (ODBC), is a database access protocol that enables you to connect to a database and then prepare and execute SQL statements against the database. It is a widely used API for accessing databases such as Oracle. In conjunction with an ODBC driver, an application can access any data source including data stored in spreadsheets like Excel. Since ODBC is a widely accepted standard, applications can be written to comply to the ODBC standard. The ODBC driver will perform all the mappings between the ODBC standard and the particular database the application is accessing. Using a data source specific driver an ODBC compliant program can access any data source without any more development effort.

    Oracle provides the ODBC interface so that applications of any type that are ODBC compliant can access the Oracle database using the ODBC driver provided by Oracle. For example, an application written in Visual Basic can use ODBC to access the Oracle database.

    Java Database Connectivity (JDBC)

    Java Database Connectivity (JDBC), like ODBC is an industry standard API that enables applications to universally access data. It defines how a Java program can communicate with a data source. It has a Java interface for connecting to an object-relational database such as Oracle from Java applications, through SQL statements. The JDBC API, is used by Java applications to embed SQL statements as Java method arguments. It enables access to virtually any data source, from relational databases, such as Oracle, to spreadsheets and flat files.

    Java based applications have gained wide spread support among application developers. Java applications, being platform independent, enable developers to write their programs once and run it on any platform without additional effort. The JDBC API is an essential interface to build Java applications. Using data source specific JDBC drivers the programs can easily connect all data sources.

    Oracle provides two types of JDBC drivers for accessing the Oracle database from Java applications:

  • JDBC Thin Drivers

    The thin JDBC driver is a pure Java Type IV driver . It can be used in Java applications, either client-side or applets, to access an Oracle database. No Oracle client needs to be installed on the client side, making it especially well-suited for Internet applications, since it can be dynamically downloaded just like any other Java applet. The thin driver establishes a direct connection to the database from the browser. This connection is stateful in a two-tier configuration. The JDBC Thin driver allows a direct connection to the database by providing an implementation of TCP/IP on top of Java sockets.

  • JDBC Oracle Call Interface (OCI) Drivers

    The JDBC OCI driver is a Type II driver. It can be used with client-server Java applications. Unlike the thin JDBC driver this driver requires an Oracle client installation. It converts JDBC invocations to calls to the Oracle Call Interface (OCI). These calls are then sent over Oracle Net to the Oracle database server. The OCI driver communicates with the server using the Oracle-developed TTC protocol. It provides a richer set of functionality and some performance boost compared to the JDBC Thin driver, however it is significantly larger in size and requires a client-side installation.

    Figure 6. below shows the JDBC Driver Architecture.


    Figure 6. JDBC Driver Architecture


    It is very common for companies to have to operate in an environment where a myriad of applications have to operate together. In fact in today’s business world it has almost become a fact of life, and companies have resigned to it since now, organizations are not only trying to integrate internally but more importantly they often need to exchange information with their customers and partners. There is also the added complexity that information needs to be shared in an environment where connectivity cannot be guaranteed, there is unpredictable latency and transactions require security.

    Oracle9i offers a great medley of features that is critical for exchange of information. It offers integration using Transparent Gateways and Generic Connectivity, the Messaging Gateway, and the open interfaces. These technologies support direct synchronous access to table data, as well as access using Oracle9i Streams. Oracle9i is a highly reliable, secure and scaleable database, which along with its integration features makes it the clear choice for all types of information sharing solutions.

  • | mail me | home page | | © is GPL | geek | search