Introduction and Troubleshooting of SOA 11g Database Adapter
By Antonella Giovannetti-Oracle on May 17, 2013
SOA 11g Adapters
Oracle SOA Suite 11g Adapters allow Middleware service engines (BPEL, BPM, OSB, etc) to communicate with backend systems like E-Business Suite, Siebel, SAP, Databases, Messaging Systems (MQSeries and Oracle Advanced Queuing), Tuxedo, CICS, etc
SOA provides different types of adapters: Technology, Legacy, Packaged Application and Others. It also allows the creation of custom adapters.
SOA 11g Database Adapter
The Database Adapter enables service engines to communicate with database end points. Databases like Oracle or any other relational database that follows the ANSI SQL standard and provides JDBC drivers. Some of the databases are:
* Oracle 8i and above
* IBM DB/2
* FOCUS Data Access
* Great Plains
* Microsoft SQL Server
* MUMPS (Digital Standard MUMPS)
* Red Brick
* SAS Transport Format
* Unisys DMS 1100/2200
* Navision Financials (ODBC 3.x)
The database adapter supports inbound and outbound interactions. It is based on standards like J2EE Connector Architecture (JCA), Extensible Markup Language (XML), XML Schema Definition (XSD), and Web Service Definition Language (WSDL).
The adapter is deployed as a RAR file into Weblogic. The figure below shows how to check the deployment. It can also be deployed in any Application Server that supports the standards. App servers like Websphere and JBOSS
Other features associated with the Database Adapter:
- Uses TopLink to map database tables and data into XML.
- Transforms DML operations (Merge, Select, Insert, and Update) as Web services. It also supports stored procedures and Pure SQL
- Supports Polling Strategies to avoid duplicate reads (Physical Delete, Logical Delete, Dequencing Tables and files)
- Supports Transactions to keep the database in a healthy. Changes to the database are rollback in case of an error
- Streaming Large Payload. Payload is not stored in memory
- Schema Validation
- High Availability. Supports Active-Active or Active-Pasive clusters
- Performance Tuning
To integrate the Database Adapter with BPEL, create a SOA composite in JDeveloper and drag and drop the adapter to the composite’s Services or Reference region. This will create a Inbound or outbound interaction respectively.
When the adapter component is added to the composite the configuration wizard will open. Through the wizard we define the connection to the database, the type of operation (insert, update, poll, etc), performance parameters, retry logic, etc.
Once the configuration is done, JDeveloper creates a series of SOA artifacts. These files are used by the composite to communicate with the adapter instance during runtime. Some of the artifacts are:
: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.
: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.
: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.
: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
<serviceName>.properties:It is created when tables are imported, and information about them is saved. Based on the properties in the
db.jcafile and the linked
<seviceName>.propertiesfile generates the correct SQL to execute, parses the input XML, and builds an output XML file matching the XSD file.
The basic step to troubleshoot the adapter is to set the oracle.soa.adapter logger level to Trace:32(FINEST) in the FMW Console
Once this is done, reproduce the issues and check the SOA Manager Server log file MW_HOME/user_projects/domains/<domain_name>/servers/<soa-server>/logs/soa-diagnostic.log
Look for JCABinding and BINDING.JCA-xxxx strings. You should see messages like these:
If a BINDING.JCA error occurred go to My Oracle Support knowledge base and search for it. Remember this is the same knowledge base used by Oracle support engineers when solving Service Requests.
- Oracle® Fusion Middleware User's Guide for Technology Adapters 11g Release 1 (22.214.171.124.0)
- Information Center: Technology & Application Adapters [ID 1355960.2]
- Master Note for recommended knowledge when working with Technology, Application and Legacy Adapters [ID 1070797.1]