Weblog for the AMIS Technology corner
Posts tagged database adapter
Implementing Web Services backed by a Database PL/SQL API using the Oracle Service Bus
Dec 1st
This article accompanies an article on the Architecture section of Oracle Technology Network (OTN): Implementing the Enterprise Service Bus Pattern to Expose Database Backed Services. It provides a detailed description of the implementation of the ESB architecture design pattern – the same that is introduced in the article on OTN – using Oracle Service Bus. This OSB based implementation is in terms of structure, architecture design and functionality the replica of the pure Java based implementation described in the OTN-article. OSB adds productivity, agility and many run time administrative benefits over the pure Java solution.
The challenge
The challenge the organization – let us call them Stuff Inc. – is facing is simple and common: external parties have requested access to information through Web Services. This information is held in the enterprise (Oracle) database at Stuff Inc.
The architecture team decides that the implementation of the service should be done according to the ESB architecture pattern.
Their initial implementation is based on custom Java Classes, XSLT stylesheets and JDBC calls.
After some time having worked with this initial set up, they decide to introduce the Oracle Service Bus to replace much of their generic classes and ESB plumbing. This article tells the story of how the OSB is used to implement a simple Web Service based on a PL/SQL API in an Oracle Database, using two transformations and a JCA Database Adapter service. It demonstrates the essential steps in implementing any OSB service.
Choosing the best way for SOA Suite and Oracle Service Bus to interact with the Oracle Database
Jun 21st
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.
Oracle 11g SOA Suite – Service-enable a Query Using the Database Adapter’s Execute Pure SQL Option
Dec 23rd
The Database Adapter is a powerful tool to expose data and PL/SQL code in your service applications. If you want to quickly service-enable a custom-made query, you definitely may want to explore the “Execute Pure SQL” option of the Database Adapter.
This tutorial will show how to service-enable a query step by step using the Database Adapter with Pure SQL.
Business Case: Retrieving Department Information
Assume you have written a query that retrieves information for departments in your database and you want to service-enable this query. The query optionally accepts two arguments: a department ID and a department name. Selection on a part of the department name is possible and selection is case-insensitive. If the arguments are both empty, all departments will be returned by the query.
The query we are talking about here does not only contain columns that are mapped 1-on-1 on database columns but also a concatenation expression and calculated columns using analytical functions. Therefore the “Execute Pure SQL” option of the Database Adapter is a logical choice to expose this custom-made query.
OOW 2009: Introducing SOA and Oracle SOA Suite 11g for Database Professionals
Oct 11th
The Oracle Open World 2009 conference is almost underway. On Monday 12th October I will do two presentations, and I am done preparing the first one of them (the one that has actually sold one – probably scheduled in a small room).This presentation is one in which I introduce the key concepts and objectives of SOA (Service Oriented Architecture) as well as the Oracle SOA Suite 11g to an audience of database professionals. Whether DBA or Database Developer, SOA is unavoidable. But what (exactly) is it? And how does it impact – positively or negatively – the work and lives of database professionals? What can a database professional do to work well with SOA and the SOA technology once that starts being implemented in her or his organization?
