Soaring through the Clouds – Part 5: Orchestration, Enrichment and Persistence with SOA CS and DBaaS

Version 7

    Part 5 in the Soaring Through the Clouds series continues its technical dive into a sophisticated demo created by several Oracle ACE Directors to allow users to nominate and vote for music artists they want to perform at a special concert. Part 5 focuses on the roles Oracle's SOA Cloud Service and Database Cloud Service play in this demo.


    By Lucas Jellema ACED.gif


    Article Series Table of Contents
    1. Introduction: How to Integrate Ten Different Oracle Public PaaS Services
    2. Internet of Things Cloud Service
    3. Building the Authorization Process and Microsite
    4. Integration Cloud Service to Connect the Dots
    5. Orchestration, Enrichment and Persistence with SOA CS and DBaaS
    6. Lightweight Application Container Cloud Service for Rich API implementation with Node.js
    7. Mobile Cloud Service
    8. Oracle JET application on top of MCS and ACC


    The solid foundation for our soaring across the clouds demo is the highlighted section in the upper right-hand corner in the graphic, below. This is where Oracle's SOA Cloud Service (SOACS) delivers some orchestration triggered by requests coming in from Oracle Integration Cloud Service (ICS) to persistently store approved candidate artists. SOA CS uses a REST-style artist enrichment API, running as a Node.js application on the Application Container Cloud, to create a very rich artist record and saves it to the database in the cloud—the Oracle Database Cloud Service (DBaaS) instance. All information on the approved artist proposals in DBaaS are exposed through a SOAP/XML web service from SOA CS. The mobile backend running on Oracle Mobile Cloud Service (MCS) is the primary consumer of this web service.


    SOA CS


    SOA CS is Oracle’s premier enterprise integration platform, SOA Suite 12c, running in the cloud. Because SOA CS is provisioned and managed by Oracle at the infra level, getting started is very straightforward. Developing for SOA CS is no different from developing for SOA Suite running on premises, so many thousands of integration developers can start implementing integrations on SOA CS. (For an overview of how to get started with SOA CS see: The powerful database adapter makes interaction from SOA CS to a DBaaS instance a breeze. Executing SQL or invoking PL/SQL from SOA CS to a database running in the cloud or on premises is simple and straightforward.


    The NewActProposalsService is implemented through a SOA Composite, shown in the figure below. This composite is developed in JDeveloper without any special provisions because of the intended deployment to the cloud. The composite is built and deployed to the SOA CS instance in the same way as for an on-premises runtime environment.


    This service exposes two operations that are both invoked from ICS: one to check if a proposal has already been registered for a certain act (the bottom flow in the figure) and another to register a new act proposal (in the DBaaS instance). Calls to both operations are routed to the Mediator component and from there either to a BPEL component or directly to a Database Adapter that invokes a PL/SQL function in the DBaaS instance.




    In order for SOA CS to interact with the DBaaS instance, a JDBC data source needs to be configured using the public IP address for the database in the cloud, very much as would be the case on premises. A database adapter connection is configured using that data source. The logical JNDI name of this connection is part of the configuration in the SOA composite. The next figure visualizes how the SOA CS instance and the DBaaS instance are part of the same Oracle Cloud identity domain where they each have their own compute and storage nodes.


    The easiest way to implement somewhat complex interactions between SOA Suite and Database is through the use of PL/SQL packages and Abstract Data Types (ADTs). A PL/SQL package defines a clear interface—the package specification—that the database adapter can readily translate into a service interface in terms of WSDL and XSD. Calling the database adapter from a BPEL process or a Mediator is no different from invoking a regular Web Service. Meanwhile, an experienced database developer can create the implementation of the package body, which can do very complex processing if needed. Advanced SQL statements, complicated business logic, multi-step transactions—PL/SQL is a rich language that can be used to realize powerful data manipulations and record retrieval.


    The BPEL process orchestrates the flow for new act proposals. First, the Artist API – exposed by a Node.js application running on ACC  - is invoked from the BPEL process, to enrich the artist proposals with such details as a biography, a genre indication, a url to a representative image (if none is provided in the request message) and a discography with the 50 most recent albums by the artist.




    Next, the BPEL process calls the PL/SQL package ACT_PROPOSAL_API through the database adapter, to have the artist proposal recorded in the database with all enriched data. The registration identifier is assigned in the database and returned to the BPEL process. The BPEL process then returns the response including that identifier. Finally, it invokes the reference to the Twitter Service exposed from ICS, as discussed in the previous installment in this series. This call causes a tweet to be published regarding this latest artist proposal.


    Enterprise Database in the Cloud: DBaaS


    Similar to SOA CS—which offers the proven, robust on-premises SOA Suite product with all the benefits from running in the cloud—DBaaS is the Oracle Database Release 12c running in the cloud. Provisioned and managed by Oracle, DBaaS is exactly the same in terms of functionality, administration and development as the on-premises counterpart. Working with DBaaS is therefore a trivial matter for the hundreds of thousands of Oracle Database specialists out there. For an introduction to DBaaS and how to get started with it, see:


    In our integrated demo flow, the DBaaS instance is the conclusion of one flow (the creation of new artist proposals that starts with the tweets analyzed by IoT CS) as well as the foundation for another: the request for artist proposal details that is initiated from the Oracle JET web application.


    The figure below shows the PL/SQL package and the two tables that form the heart of the database implementation. All that the SOA composite’s database adapter needs to know and care about, by the way, is the PL/SQL package specification for ACT_PROPOSAL_API.



    The use of the ADTs (aka user defined types or UDTs) allows this PL/SQL interface to make use of complex, nested data structures with collections at multiple levels. The API invoked from the NewActProposalsServices SOA Composite makes good use of custom types to transfer a complex data structure for persistently storing the enriched artist proposal. Here is the package specification:


    create or replace
    package act_proposal_api
    procedure submit_act_proposal
    ( p_description in varchar2
    , p_number_of_votes in number
    , p_image in blob
    , p_artist in artist_t
    , p_id out number
    procedure verify_proposal_for_act
    ( p_act_name in out varchar2
    , p_act_proposed_yn out varchar2
    , p_registration_date out date
    , p_description out varchar2
    , p_image_url out varchar2
    , p_number_of_votes out number
    , p_id out number
    end act_proposal_api;


    The artist_t type is in fact a nested ADT definition, using the album_t type and the discography_t collection type:


    create or replace
    type album_t as object (
    title varchar2(200)
    , release_date date
    , cover_image_url varchar2(500)
    create or replace
    type discography_t as table of album_t
    create or replace
    type artist_t as object
    ( name varchar2(200)
    , genres varchar2(200)
    , biography varchar2(4000)
    , image_url varchar2(1000)
    , albums discography_t


    With these types, the SOA Suite database adapter can transfer master and associated detail records in a single database round trip. Note that the artist_t definition could easily be extended to many more levels; the PL/SQL as well as the database adapter could easily handle such complexity.


    Deploy and Run


    The objects in the database are created with two DDL scripts (see the associated sources for this article) that are executed through SQL Developer, which has a direct database connection through the public IP address of the DBaaS instance.


    The SOA Composite is built and deployed to a JAR file (aka a SAR) from JDeveloper. This file is deployed to SOA CS from the Enterprise Manager FMW Control browser interface. Immediately after deployment, the web service is available for action from ICS and first, for example, for test calls from Soap UI.


    The message flow trace for this call in the Enterprise Manager console for SOA CS would look like the next figure:


    The detailed BPEL flow trace for this instance is shown in the next figure. If needed, we can drill down in each of the activities to inspect the data in variables, call outs and response messages.


    The effect of this activity is twofold. New records are created in the DBaaS tables.


    A Twitter message is also published to let the world know about the new artist proposal, just as was described in the previous installment on ICS.




    Oracle SOA Suite 12c and Oracle Database 12c are two rock-solid products that thousands of companies use in their day-to-day operations—in their on-premises data center. With SOA CS and DBaaS, these same products are available from Oracle in the form of PaaS Cloud Services. Developing for SOA CS and DBaaS is exactly the same as developing for on-premises SOA Suite and Database ; even the deployment and administration are almost identical. Getting access to instances of these two cloud services is easy, as is making them interact. In our across-the-cloud integrated demo, using SOA CS and DBaaS proved the easiest bit.


    Continue to Part 6: Lightweight Application Container Cloud Service for RICH API Implementation with Node.js


    About the Author


    Oracle ACE Director Lucas Jellema is solution architect and CTO at AMIS, based in the Netherlands. An Oracle, Java, and SOA specialist, he works as a consultant, architect, and instructor in diverse areas such as Database & SQL, Java/Java EE, SOA/BPM and PaaS and SaaS Cloud Solutions. The running theme through most of his activities is the transfer of knowledge and enthusiasm (and live demos). Lucas is a well-known speaker at Oracle OpenWorld, JavaOne and various Oracle User Group conferences around the world. His articles have appeared in OTN, OTech and the AMIS Technology Weblog, and he is the author of Oracle SOA Suite 11g Handbook (2014, McGraw Hill) and the Oracle SOA Suite 12c Handbook (2015, McGraw Hill).


    NOTE: This article represents the expertise, findings, and opinion of the author.  It has been published by Oracle in this space as part of a larger effort to encourage the exchange of such information within this Community, and to promote evaluation and commentary by peers. This article has not been reviewed by the relevant Oracle product team for compliance with Oracle's standards and practices, and its publication should not be interpreted as an endorsement by Oracle of the statements expressed therein.