Saturday, January 28, 2012

Start Small, Grow Fast | Companion Checklist

A companion checklist that summarizes the Oracle SOA Suite best practices contained in the "Start Small, Grow Fast" Oracle whitepaper by Demed L'Her, Edwin Biemond, and Ronald van Luttikhuizen at

Administrative considerations

  • Involve operations teams from the beginning.
  • Make sure your organization has dedicated middleware administrators.
  • Prefer scripting for deployment of composites.
  • Use SOA Suite configuration plans to capture all environment-specific information instead of hardcoding them in your composites.
  • Avoid creating a separate configuration plan per composite per environment - consolidate these to a few or a single configuration plan per environment.
  • Script as many configuration tasks as possible from the beginning.
  • Document configurations in a well-known central location such as a team Wiki, readily available to all stakeholders.
  • Consider using NFS shares for JCA adapter configuration plans.
  • Use partitions to categorize composites and execute various tasks for multiple composites at once.
  • Use composite sensors to enable the search of specific instances.
  • Negative testing is an absolute requirement.

Infrastructure considerations

  • Build a cluster from the start -even if only with a single node. Think of the following when configuring a cluster:
    • Use individual IP addresses for all components (WebLogic Admin Server, Node Manager, and the Managed Servers).
    • Use the JRockit JVM and Mission Control to detect problems and perform tuning.
    • Leverage WebLogic channels to separate cluster traffic from production traffic.
    • Use a load balancer to divide traffic between services and detect outages of servers in the cluster.
  • Think about your domains. There is no such thing as a “one-size fits all” domain topology; however there are a few tips and guidelines in this area:
    • Always setup Node Manager.
    • Do not combine Admin and Managed Servers; except maybe in non-critical environments such as development.
    • Deploy your software and services to Managed Servers.
    • Leverage domains to partition environments with different lifecycles or significant functional differences.
    • Dedicate the most performant hardware to Managed Servers.
  • Linux x86 is a great entry-level platform for Oracle SOA Suite.
  • Favor Oracle Database as infrastructure database.
  • Perform realistic load tests.
  • Define and test a purging procedure well before going live.

Design-time considerations

  • Use MDS to centrally store artifacts and avoid duplication.
  • Have a canonical model for your core objects to ensure future re-use and consistency.
  • Consider taking a contract-first or meet-in-the-middle approach for interfaces exposed to the outside world.
  • Formulate and adhere to a small set of service design guidelines and naming conventions:
    • Language conventions
    • Naming conventions for composites, services, references and components
    • Naming conventions for BPEL and BPM components and activities
    • Naming conventions for composite sensors
  • Wrap frequently used SOA Suite APIs in simpler custom APIs.
  • Use Domain-Value Maps (DVM) and Business Rules to improve flexibility and agility.
  • Use fault policy files to separate exception and fault handling from “normal” process logic. Centrally store them in MDS to enable re-use across composites.
  • Unit test your composites using SOA Suite’s test framework as you would unit test your Java code using e.g. JUnit.

Architectural considerations

  • Use a simple and concrete service categorization.
  • Spend some time thinking about the granularity of your services to avoid too frequent refactoring. Reusability is a key factor. Other factors are rate of change, availability, and ownership.
  • Building asynchronicity through business events or messaging will improve loose-coupling, ease of deployment and administration and enable throttling.
  • Consider supporting multiple versions of the same service in production to allow service consumers to upgrade at their own pace.
  • SOA governance is needed but can start very simply with a wiki. Consider a full-fledge repository as you grown your SOA efforts.

Friday, January 27, 2012

Using custom code in Oracle Integrated SOA Gateway

In a previous post I discussed how to expose seeded APIs in Oracle EBS 12 as a Web Service.

But, as you can guess, seeded APIs are not always the easiest way to access EBS. For example, the PAY_ELEMENT.CREATE_ELEMENT_ENTRY procedure expects the programmer to take care of EBS specific arguments like element_link_id.
Our self service application has logic to submit, approve and check expenses. To do that, it uses employee ids and business rules about submitting, approving and checking steps. Users can view the state, get tasks assigned to them and can save their expenses in the application. This is setup using BPM, business rules, Java and ESB technologies. This application is blissfully ignorant about element_entry_link_ids, date tracking implementations etc. On top of that, EBS does not provide "select" APIs to get data from the application. We don't want to create SQL statements in our application, we like to hide the implementation and use APIs to communicate with EBS.

The good news is, Oracle E-Business Suite 12 provides a way to make custom code part of the integrated SOA Gateway.

This way you have the best of both worlds: monitoring and control of the services with the Integration repository and usable APIs for external applications. Let's see how that works.


The following steps have to be executed to accomplish this:

  1. Write your custom API
  2. Test it
  3. Annotate Custom APIs
  4. Validate Annotated Files
  5. Upload Validated Files
  6. Create Security Grants
  7. Generate Web Services
  8. Deploy Web Services

From: Integrated SOA Gateway Developer's Guide

Writing and testing PL/SQL code is beyond the scope of this blog and the last three steps were described in the previous post, so I will skip those steps here.

Documentation and roles
There are two roles involved: a system integration developer and a system integration administrator. The 'Integrated SOA Gateway Developer's Guide' describes how the annotations should be added to the package specification in the Chapter 'Creating and Using Custom Integration Interfaces'.  Appendix A 'Integration Repository Annotation Standards' describes the annotations in detail. The 'Integrated SOA Gateway Implementation Guide' describes how to validate and upload the files.

Annotate Custom APIs
A system integration developer annotates the API that needs to be loaded in the integration repository. The integration repository parses the source files to populate itself.
So to populate the integration repository with your own Custom interfaces, you only need to annotate the package specification, not the package body.  The syntax is similar to JavaDoc annotations: @NameSpace:TypeName keyString
First of all, you need to annotate the package, with a scope, the product name, the displayname and the category. Secondly, you annotate the procedures that you want to expose as a Web Service. The annotations for the procedure should be placed between the definition and ';'.

create or replace package xxgr_soa_pay_elements is
/* $Header: xxgr_soa_pay_elements $ */
* This custom PL/SQL package can be used to add pay elements. 
It hides some particulars of the PAY_ELEMENT_ENTRY_API
* @rep:scope public
* @rep:product PER
* @rep:displayname Create Pay element entry
procedure create_element_entry
( p_employee_number        in number
, p_element_name           in varchar2
, p_element_eff_start_date in varchar2
, p_element_eff_end_date   in varchar2
, p_entry_type             in number
, p_input_value_name1      in varchar2
, p_entry_value1           in number
, p_input_value_name2      in varchar2 default null
, p_entry_value2           in number   default null
, p_input_value_name3      in varchar2 default null
, p_entry_value3           in number   default null
, p_date_earned            in varchar2 default null
, p_subpriority            in number   default null
, p_effective_start_date   out varchar2
, p_effective_end_date     out varchar2
, p_element_entry_id       out number
, p_object_version_number  out number
, p_create_warning         out number

* Use this procedure to create a single entry for an expense. NB
* this procedure needs to be optimized to use an array for the flex fields.
* Date formats are dd-MM-YYYY
* @param employee_number employee number that the pay element applies to
* @param element name name of the pay element, type of expense
* @param  p_element_eff_start_date  effective start date of the pay element. 
* @param p_element_eff_end_date effective end date of the pay element
* @param  p_entry_type a constant 'E' for earnings
* @param p_input_value_name1  label of the first entry
* @param p_entry_value1  value of the first entry, the amount that is expensed
* @param p_input_value_name2  label of the second entry optional
* @param p_entry_value2  value of the second entry, optional
* @param p_input_value_name3  label of the third entry, optional
* @param p_entry_value3 value of the third entry, optional
* @param p_date_earned  date that the value is earned. Optional
* @param p_subpriority priority of the pay element, optional
* @param p_subpriority            in number   default null
* @param p_effective_start_date returns the effective start date
* @param p_effective_end_datereturns the effective end date
* @param p_element_entry_id  the number of the pay element (expense) that is 
* created
* @param p_object_version_number the version number of the object
* @param p_create_warning flags whether warning is created.1=true, 0=false
* @rep:displayname Create a pay element (expense)
* @rep:category BUSINESS_ENTITY 
* @rep:scope public
* @rep:lifecycle active
end xxgr_soa_pay_elements;

Validate Annotated Files
The integration repository administrator validates the annotated files before uploading them to the repository. This is done using the Integration Repository Parser, a standalone design time tool. While executing the parser, the annotated source files are validated based on the interface type supported for customization. If no error occurs, an Integration Repository loader file (iLDT) will be created. However, before you can start, you need to setup the environment: install Perl modules and possibly install some patches (depending on the EBS12.x version you are using).
Once you installed the necessary Perl modules, you execute the command:

$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/ -g -v
-username=<a fnd username> <product>:<relative path from product
top>:<fileName>:<version>=<Complete File Path, if not in currect

Upload Validated Files
If the annotated files were valid, they can be uploaded to the integration repository by someone with the rol integration repository administrator.

Not that for an object (or class) that is already present in the Integration Repository, the Integration Repository Loader program reloads the new definition of that object ONLY if the new version is greater than the current version. Make sure you increment the Header version of the target source file.
Note that there is no GUI for uploading validated files. You can yse Telnet to have command access to the Oracle E-Business Suite Release 12 instanc and use the following command to upload the iLDT file:

$FND_TOP/bin/FNDLOAD <db_connect> 0 Y UPLOAD $fnd/patch/115/import/wfirep.lct <ildt file>

We showed a PL/SQL example here, but the same procedure applies to Java classes, or other supported interface types. Check the developers guide and the implementation guide for a list of supported interface types.

As I stated in my previous post, in order to successfully interface with an Oracle E-Business Suite instance, it is critical to cooperate with the developers and administrators of the Oracle E-Business Suite. That way you ensure that the interfaces are both manageable for the people responsible for running and maintaining the Oracle E-Business suite implementation and maintainable and usable for the developers of the self service application. When executed this way, Oracle E-Business Suite becomes a first class citizen of the 'SOA-World' we live in nowadays.

Tuesday, January 24, 2012

Oracle, Webservices, and PL/SQL

There are many ways you can publish or consume Web Services or XML Documents in an Oracle environment. The other day an organization asked me to evaluate the solution they had chosen to expose PL/SQL packages as Web Service and to consume Web Services from PL/SQL. Not because they experienced problems, but because they wanted to know the 'current state of affairs' in Oracle and XML and evaluate if their way of solving this was the most efficient way.

Not surprisingly, the possibilities are endless, depending on your environment. In this blog, I have listed the possibilities I know of for PL/SQL and added some details about when you would like to use the feature.

Publishing Web services
The following frameworks are available to publish a PL/SQL procedure or function as a web service:

Native Database web services Oracle Database 11g Oracle XML DB 11g
PL/SQL Webservices Oracle Applicatieserver JPublisher, SQLUTL
Eclipselink-DBWS JEE Applicatieserver
  • DBWSBuilder (DesignTime)
  • JAX-WS (Runtime)
Oracle DB Adapater Oracle SOA Suite Toplink
Integrated SOA Gateway Oracle E-Business Suite 12  Oracle SOA Suite 10g

The Integrated SOA Gateway is only a viable option, if you are exposing PL/SQL APIs that are part of EBS 12.

If you don't have an JEE Application server (for example because you are in a .NET programming environment), Native Database web services are a viable option.

Of course, you can also program a web service in .NET or Java and call the PL/SQL procedure or function using Object Relational mappings.

Consuming Web services
The other way around, calling a Web Service from the database is also a common use case if you have a lot of logic in your database.

PL/SQL Oracle Database 11g UTL_HTTP
Java Oracle Database 11g Java stored procedures, JAX-WS
JPublisher Oracle Database11g UTL_DBWS

Although I think that BPEL, Java and .Net frameworks are a better fit for this type of thing, it sometimes is not feasible to change the entire software architecture. Using a utility like UTL_DBWS and JPublisher is very convenient and less error prone than coding all the XML by hand in the database.
If you are planning to move to JEE or SOA, using PL/SQL Webservices, EclipseLink or the Oracle database adapter is a good step to start this endeavor.

Monday, January 2, 2012

Using SOA Gateway in EBS 12

A lot of organizations use a packaged application for their supporting processes, like Human Resources and Finances. Often you need to integrate with these systems. There are several use cases for this:
  • Use data from the HR system to determine hierarchy;
  • Use functionality of an ERP module in another process;
  • Expose services for self service purposes.
Oracle E-Business suite has built-in support for these types of integration. In this post we will look at an example of the third use case: a self service application for expense reports, that stores the result (a pay element) in the E-Business Suite.
There is a lot of documentation available, so far I have used:
  1. Oracle® Integration Repository User's Guide
  2. Oracle® E-Business Suite Integrated SOA Gateway Developer's Guide
  3. Oracle® E-Business Suite Integrated SOA Gateway User's Guide
  4. Integrated SOA Gateway Implementation Guide
  5. Oracle® Application Server Adapters for Files, FTP, Databases, and Enterprise Messaging User's Guide
  6. Oracle Human Resources Management Systems Workforce Sourcing, Deployment, and Talent Management Guide
This can make it rather overwhelming (to put it mildly ;) ) This blog gives an overview of important steps and the documents that contain information about these steps.

Like any other feature in the Oracle EBS, you need to have the right privileges to use the SOA Gateway.

Therefore, you have to create a user that has the correct permissions to generate and deploy a web service. The following table can be found in theOracle® E-Business Suite Integrated SOA Gateway User's Guide:

System Integration Analyst
System Integration Developer
Integration Repository Administrator
View public interfacesYesYesYes
View private/internal interfacesNoYesYes
Generate/regenerate Web Services (WSDL)NoNoYes
Deploy/redeploy Web ServicesNoNoYes
Undeploy Web ServicesNoNoYes
Subscribe to business eventsNoNoYes
Create GrantsNoNoYes
Download Composite servicesNo (configurable)YesYes

In my experience it is easiest to just use the Integration Repository Administrator role in the development environment. If you want to limit the number of people that can actually create the web services you can do that in the testing, staging and production environment.

In the Integrated SOA Gateway Implementation Guide it is explained in "Setting up Oracle E-Business Suite Integrated SOA Gateway" how you can set up these roles.

Generate the WSDL
To generate the WSDL, navigate to the PL/SQL API in the Integration Repository.  In the project we generated a WSDL for PAY_ELEMENT_ENTRY_API, the function CREATE_ELEMENT.

Oracle® E-Business Suite Integrated SOA Gateway Developer's Guide describes in "Using PL/SQL as Web services" how you can accomplish this. In the screen shot below, you see what it looks like for the HR_EMPLOYEE_API for the function CREATE_EMPLOYEE.  (NB: I use the HR_EMPLOYEE_API example in the screen shots, because in our environment we already generated the WSDL for the PAY_ELEMENT_ENTRY, so the screens look different).

Now you can view the WSDL in your browser, by selecting: "View WSDL".  The Web Service status is added and is set to "Generated"

Deploy the WSDL
Once you have generated the WSDL, you need to actually deploy the webservice. This will install the required files on the Oracle Application Server. 
In this example we use username tokens as authentication mechanism, but SAML tokens are also supported. 

Grant Access 
Now you have to grant access to the webservice by using the "Create Grant" option. The best way to handle this, is to create one user for example "selfsvc" that has access to the webservices on behalf of the users that use the self service application. 

Call the Web Service
SQLJUTL is used when generating the web services from PL/SQL. This is the same technology that is used by the Oracle Database adapter and Eclipselink DBWS.  This requires some specific knowlegde. For example a PL/SQL boolean is generated as an integer:
<element name="P_VALIDATE" type="int" db:index="1" db:type="INTEGER" minOccurs="0" nillable="true" /> 
Documentation about the mapping form PL/SQL to XML can be found in  Oracle® Application Server Adapters for Files, FTP, Databases, and Enterprise Messaging User's Guide: "Acceptable values for the original BOOLEAN parameter now that it is an INTEGER are 1 for true and 0 for false. Any value other than 1 is considered false. The generated wrapper procedure uses APIs from the SYS.SQLJUTL package to convert from INTEGER to BOOLEAN and vice-versa."

Calling the webservice requires both knowledge of the implementation of the EBS module and knowledge of XML and Web Services. For example, it expects an assignment_id and an element_link_id as a parameter, it uses date-tracking and has the option to validate the call, instead of actually executing (committing) the data. 
Information about this can be found in the "Oracle Human Resources Management Systems Workforce Sourcing, Deployment, and Talent Management Guide". This guide explains for example the assignment concept: 
"The assignment is the central concept that relates employees to the structures in which they work, and the compensation and benefits for which they are eligible.
In Oracle HRMS, many of the activities you undertake in human resource management, such as vacancy management and budget planning, are based around assignments and not people. In particular, you enter all earnings, deductions, and other pay-related elements for the employee assignment, rather than the employee. This makes it possible to give an employee two or more assignments when this is necessary.
For example, if an employee has more than one role within your enterprise, he or she can have a separate assignment for each role."

You can test the web service using SOAPUI, or the standard test client on the Oracle Application server.
To test it with SOAP UI, you need to import the WSDL. You can find the address of the WSDL when you click on "View WSDL". Just copy the url in your browser into SOAP UI. It will look something like this:

To test the web service in your browser, put the link in your browser, without the '?wsdl'

This will give you a webpage to test the service with:

We needed to install a number of patches before we were able to successfully use the SOA Gateway, but once the whole thing is setup (patches, users privileges etc), creating the web service is trivial. A very important benefit is that it is visible in the integration repository that a Web Service is generated, based on the PL/SQL API. This is much harder to keep track of when you use a Database adapter, Eclipselink DBWS or the Oracle Apps adapter. 
Knowledge of the PL/SQL APIs in EBS is a must in your project. In our project we worked closely with the EBS functional and technical people to figure out how to use the PL/SQL APIs correctly and what APIs we needed. The biggest challenge in this type of project is to combine knowledge of EBS and PL/SQL with knowledge of Web Services and integration.  
What is seriously lacking in the PL/SQL APIs are simple 'read functions'. So if the first use case applies (using data from HR), you need to write your own APIs or call the database tables directly. The latter is a bad idea in my opinion. This mean that you always have to write some APIs yourself when interfacing with EBS.  In the Oracle® E-Business Suite Integrated SOA Gateway Developer's Guide you can find a description how you can add these custom interfaces to the repository and generate WSDLs from them in chapter "Creating and using custom integration interfaces".