Oracle JCA Adapter for Database

Introduction to the Oracle Database Adapter

The Oracle Database Adapter enables a BPEL process to communicate with Oracle databases or third party databases through JDBC. The Oracle Database Adapter service is defined within a BPEL process partner link by using the Adapter Configuration Wizard of Oracle BPEL Process Manager (Oracle BPEL PM).

This section includes the following topics:

• Functional Overview

• Design Overview

Functional Overview

The Oracle Database Adapter enables Oracle SOA Suite and Oracle Fusion Middleware to communicate with database end points. These include Oracle database servers and any relational databases that comply with ANSI SQL and provide JDBC drivers.

The principle of the tables and views in the Oracle Database Adapter is to expose to SOA tables and SQL as transparently and non-intrusively as possible. From an integration standpoint, tables and SQL are what relational database products have in common, so a generic solution focused on what is standard has the greatest reach. In exposing databases to SOA, it is also about combining the technologies of SQL and XML, the former an ideal language for querying information, the latter an ideal format for transporting and representing information. While stored procedure support is less standard across databases, Oracle Database Adapter provides support for stored procedures as the guide describes.

The Oracle Database Adapter is a JCA 1.5 connector, which runs on the Oracle Application Server. It relies on an underlying JDBC connector/driver to enact the database communication. In contrast to JDBC, it is non-programmatic. The interaction (series of SELECT, UPDATE, INSERT) is loosely modeled using the Adapter Configuration Wizard. The inputs/outputs are XML, most easily seen as input parameters and result sets converted to XML. These XML inputs and outputs allow the Oracle Database Adapter services to be plugged into Oracle Fusion Middleware.

To access an existing relational schema, you must create a new application and an SOA project to use the Adapter Configuration Wizard to perform the following:

• Import a relational schema (one or more related tables) and map it as an XML schema (XSD)

  For more information, see Relational-to-XML Mapping.

• Abstract SQL operations such as SELECT, INSERT, and UPDATE as Web services

  For more information, see SQL Operations as Web Services.

• Have database events initiate an Oracle Fusion Middleware process.

The Oracle Database Adapter can currently be used only within the context of an SOA process as Oracle Database Adapter Integration with Oracle BPEL PM describes.

Although Oracle Streams Advanced Queuing (Oracle AQ) is an Oracle Database feature, you use the separate, specialized Oracle JCA Adapter for AQ to integrate with Oracle AQ. For more information, see Chapter 7, "Oracle JCA Adapter for AQ".

For non-relational and legacy systems (with a few exceptions such as DB2 on AS/400), application and mainframe adapters are available. For more information about application and mainframe adapters, see:

• Legacy Adapters

• Packaged-Application Adapters

• Oracle Adapter for Oracle Applications

For more information on the Oracle Database Adapter, see:

• Oracle Database Adapter Features

• Oracle Database Adapter Concepts

• Complete Walkthrough of the Adapter Configuration Wizard

Oracle Database Adapter Integration with Oracle BPEL PM

When the Oracle Database Adapter is used to poll for database events (usually an INSERT operation on an input table) and initiate a process, in a Mediator component or an SOA composite it is called an exposed service. In Oracle BPEL process it is a partner link tied to a Receive activity. The expression inbound (from database into SOA) is commonly used.

When the Oracle Database Adapter is used to invoke a one-time DML statement such as INSERT or SELECT, in a Mediator component or an SOA composite, it is called a service reference. In Oracle BPEL process, it is a partner link tied to an Invoke activity. The expression outbound (from SOA out to the database) is used.

Design Overview

Figure 9-1 shows how the Oracle Database Adapter interacts with the various design-time and deployment artifacts.

Figure 9-1 How the Oracle Database Adapter Works

Description of "Figure 9-1 How the Oracle Database Adapter Works"

The Oracle Database Adapter is a JCA 1.5 connector, which is deployed to the application server during installation.

The Oracle Database Adapter consists of multiple instances; each instance represents a connection to a database end point. Different SOA processes may point to the same adapter instance (database), while different service endpoints in a SOA process may point to different adapter instances (databases).

Because each adapter instance points to a single database, there is a one-to-one correspondence from adapter instances to application server data sources. Out of the box there is a single Oracle Database Adapter instance named eis/DB/SOADemo, which points to the data source jdbc/SOADataSource.

The list of adapter instances is stored in a deployment descriptor file, weblogic-ra.xml on Oracle WebLogic Server. (It is inside of DbAdapter.rar, which contains also the Java class files in DBAdapter.jar). Configuring an Oracle Database Adapter instance is more about creating the underlying data source: getting the correct JDBC driver and connection URL.

For more information, see JDBC Driver and Database Connection Configuration.

However weblogic-ra.xml entries occasionally have more than simply the name of the underlying data source. These properties are detailed further under Deployment.

While at run time you have Oracle Database Adapter instances, at design time you have the Adapter Configuration Wizard (link). You can run it once to generate a single adapter service end point, and then multiple times in edit mode to make incremental changes to each. It generates all the adapter related artifacts needed when deploying a SOA composite as Table 9-1 lists.

Table 9-1 Adapter Configuration Wizard Generated SOA Composite Adapter Artifacts

File	Description
<serviceName>.wsdl	This is an abstract WSDL, which defines the service end point in terms of the name of the operations and the input and output XML elements.
<serviceName>_table.xsd	This contains the XML file schema for these input and output XML elements. Both these files form the interface to the rest of the SOA project.
<serviceName>_or-mappings.xml	This is an internal file. It is a TopLink specific file, which is used to describe the mapping between a relational schema and the XML schema. It is used at run time.
<serviceName>_db.jca	This contains the internal implementation details of the abstract WSDL. It has two main sections, location and operations. Location is the JNDI name of an adapter instance, that is, eis/DB/SOADemo. Operations describe the action to take against that end point, such as INSERT, UPDATE, SELECT, and POLL. The contents of the db.jca file are wholly determined by choices made while running the Adapter Configuration Wizard.
<serviceName>.properties	This is also an internal file. It is created when tables are imported, and information about them is saved. It is used only at design time. At run time, the location is used to look up the adapter instance which executes the service. Based on the properties in the db.jca file and the linked or-mappings.xml file, <seviceName>.properties file generates the correct SQL to execute, parses the input XML, and builds an output XML file matching the XSD file. To execute the SQL, it obtains a pooled SQL connection from the underlying data source.