In the past few weeks, I have encountered a similar discussion in various organisations. Each organisation uses either SOA Suite (11g) or Oracle Service Bus (11g) at the core of their SOA infrastructure – either for integration purposes, for workflow and process orchestration or for both. In each organisation, the role of one or more databases is crucial and interaction between the SOA environment and the database is one of the most common and therefore important functions in their enterprise IT environment.
Given the importance, it is only logical that the way(s) chosen for linking the SOA component to the database is carefully selected – and evaluated every now and again. Technology may have progressed, experience may have taught us a lesson or two, the knowledge and skills may have evolved.
Interestingly enough, the number of ways for SOA Suite and OSB to communicate with the database is quite high. This article includes an illustration that shows over 20 different interaction channels that we can choose from with a fairly wild variation of attributes, required skills, productivity and performance characteristics.
Among the choices we face is the question of the communication protocol to use for accessing the database. Roughly speaking, we can go through JDBC (via a JDBC Data Source on the WebLogic Server), via HTTP or using ‘another’ channel such as File System, FTP, Email, XMPP (Chat) etc. The communication protocol may in part determine another choice that awaits us: what will be the format and nature of the messages that we exchange: text based (XML, JSON, CSV) or strongly (Oracle-ly) typed. We know that on the one hand we have the world of XML in the SOA middleware components, and on the other side the relation world of the Oracle database – at least relational and strongly typed at the core.
So when choosing the method for having the SOA components interact with the database, we have to decide on the communication protocol and we have to determine where in the route from table to SOA component (and vv.) the conversion from Relation to XML or from XML to Relational will take place.
Considerations when making that decision include the question who has to do the work of making the XML <=> Relational conversion at design time and at run time – what is the productivity of an approach and what the performance impact. Other considerations include robustness and agility or ability to maintain and evolve the implementation. Of course the required skills factor into this decision as well as the complexity of the architecture, the design, development, test and deployment process and the nature of the interaction: read only, data manipulation, small or large documents, synchronous or asynchronous (or even fire and forget) etc.
Without too much clarification, I will show an overview of the most prominent interaction channels that the Oracle Database offers for the type of programmatic interaction we need to integration with the SOA component. Note that the EPG (Embedded PL/SQL Gateway) and Native Database WebService facilities both expose an interface through HTTP. Various facilities are available to communicate via a file system – like utl_file, the BFILE data type and the URITYPE types. XMLDB provides a repository that allows the database to expose itself as an FTP Server and WebDAV repository (which can be regarded as a file share on some operating systems). Using Java Stored Procedures, interaction with file systems and other repositories and server types (including email and XMPP – for chat or instant messaging) are possible.
The majority of interactions with the database with SOA environment is usually based on JDBC which supports direct access of PL/SQL Stored Procedures as well as SQL executed against Views and Tables.
Looking at the other end of the equation, we see the SOA infrastructure. In an Oracle Fusion Middleware based environment, this consists of WebLogic Server that contains either (or both) SOA Suite or Oracle Service Bus. These products can make use of a wide range of technology adapters that allow interaction via a plethora of protocols – from JDBC to REST-style HTTP and from FTP to JMS. All these JCA adapters share a common characteristic: they perform the conversion from the protocol specific world (relational data over JDBC, JMS messages, AQ messages, Java Objects over EJB) to the world of XML. Developers will be confronted with XML inside SOA Suite components or Service Bus projects, thanks to that built-in conversion in the technology adapters.
In addition to the SOA Suite, OSB and technology adapters, the WebLogic Server may contain Java Based WebServices – including the special type of ADF BC SDO WebServices – that typically communicate via JDBC to a database and expose data and operations via SOAP/WS* WebServices. A special type of Java WebService in this picture is the type generated by JPublisher – for example from within JDeveloper – to expose the functionality of a PL/SQL package in the form of a WebService – that runs on the middle tier. This is conceptually very similar to Native Database WebServices – with similar WSDL and XSD generated for a package. However, the latter of course run on the database infrastructure.
Other Java applications running on WLS may expose an EJB interface. Such applications typically use JPA (and JDBC) to interact with the database.
Bringing the two worlds together
Having introduced the channels exposed by the database and the types of components that are available in the WebLogic Server middleware environment, we can now bring the two together, to get an overview of the most relevant interaction options that are available to us. See this next picture:
Note how the red lines indicate interaction in terms of strong, Oracle specific types (only over JDBC) with the blue lines signifying communication based on text, such as XML and also JSON or even CSV or plain ASCII.
