Tips & Tricks when working with Oracle BPEL

Today I was planning to rapidly demo the features of the DB Adapters in Bpel with a small test-case where master-detail data needs to be inserted … But I already ran into strange behaviour.

In this blog-posts I want to give the audience some valuable tips & tricks for common ‘problems’ faced when designing and deploying bpel processes.

  • Problem: Soa Suite Crashes when instantiating Simple Oracle Bpel Process with following exception ‘java.lang.Exception: Failed to create “java:comp/env/ejb/local/CubeEngineLocalBean” bean; exception reported is: “javax.naming.NameNotFoundException: java:comp/env/ejb/local/CubeEngineLocalBean not found in DeliveryBean
  • Solution: In most cases this exception occurs when you’re desiging a bpel process with a newer version of Jdeveloper than the Oracle BPEL version you’re deploying to. In my case I was designing with Jdeveloper and deploying to Oracle Soa Suite The problem was solved when I worked with an earlier version of Jdeveloper, namely
  • Problem: ‘ORABPEL-11627: Mapping Not Found Exception’, when using DB Adapter in Bpel Process. I’m trying to insert master-detail data using a synchronous bpel process and the toplink mappings where all set. When I instantiate the bpel process I’m always running into the ORABPEL-11627 exception.
  • Solution: In my case this exception was thrown because I tried to assign my person-element to a personCollection variable. When I removed the assign-activity and added a transform-activity to map the one-element xsd file to the collection-element xsd file, the exception wasn’t thrown anymore. Note: When you work with the DB Adapter, the payload of the operation is always a collection-element, so you need to map your request- or inputVariable accordoingly by using a transform-activity.
  • Problem: Toplink warnings are shown when Db Adapter-partnerlink has been defined for inserting master-detail data. Some of the warnings shown: ‘Method accessors have not been selected.’
  • Solution: This is due to a JDeveloper bug in generating toplink mapping files. You will need to change the Toplink Mapping file for those Objects that use a Collection-attribute for master-detail data. Go to your toplink-mapping file, it will show up in the Structure window. Go to the Object that holds collection-elements, the Mapping for the collection-element will show up in the main screen. You will find that Use Method Accessing is selected yet there is no Get or Set methods defineds. Just unselect Use Method Accessing, save and rebuild. Note: When doing this you need to go through the DBAdapter-wizard again so the changes are reflected properly in all needed xml- and wsdl-files. (Thanks to user: Hongdo on the Bpel-forum on OTN, for sharing this with the community !)
  • Problem: Use db-sequences when trying to insert master-detail data using one insert-operation defined through a DBAdapter-partnerlink.
  • Solution: Define the master- and detail-tables properly in the DB Adapter-wizard, make sure to define the master-table correctly so the relationships are properly read and defined by Toplink. In the generated Toplink Mapping File you need to define you want to use ‘native sequencing’ and you need to make sure the ‘pre-allocation’ size matches the increment of your DB Sequence. By default the pre-allocation size is set to 50, which probably needs to be updated to 1. The next step is to define sequencing option for both master- and detail-data on the different POJO’s. There you need to specify the database sequence to use for each primary key field you want to populate.

I’ll keep you posted when I encounter more issues in defining, deploying and running bpel processes.

Spice Up Your Oracle Knowledge – SOA EXSite – A Practical View On Oracle ESB

Oracle and Distrilogie are presenting different “Monthly Oracle Partner Training” programs and the first month (September) the focus lies on SOA.
This is a partner-dedicated event aimed at informing the Partner community about Oracle Acquisition strategy and “Best-in-Class” offerings that are critical to meet the customer needs.

My task was to give the partners a practical view on Oracle Enterprise Service Bus using a pre-sales case I’ve worked providing an answer to the customers’ specific technical requirements.

This presentation first gives an insight on the business requirements stated by the customer and then focuses on the business value of ESB for each of these requirements.

  • The first requirement was regarding the ability to work with one General Business Object (GBO) that described the customers’ data model. The answer in ESB is using an XML Schema Definition File (xsd) where we describe the required data in an hierarchical, nested manner. Given the customer the possibility to work with the same object throughout the whole business process.
  • The second requirement ‘How can we make a synchronous call from existing applications to an ESB Service?’ ‘How can we propagate changes to our different systems’? The first answer uses file- and ftp-adapter to bacth-read a CSV file. The file is polled for using the file adapter, format the file in a required format using the transform-functionality of ESB and put this file on the ftp-server of the client. For the second question regarding propagation I’ve given a demo where a JMS queue will listen for new messages. Once a message has been received a call will be made to a procedure on the database to unpack the message and create a new record. In this demo I’ve also mentioned the managed- and non-managed approach when implementing ESB Services which explains the virtualization-features of ESB.
  • The 3d requirement was regarding two-way synchronous services were I’ve integrated the ESB Services with an ADF Faces Web Application. Giving the customer an insight on the ease of integration between Oracle ADF and ESB Services using the data binding layer on Web Services.
  • In the last requirement the customer was curious about the integration-possibilities of web services and database procedures, jobs. The question that popped their mind: ‘Can we schedule a web service call using a database job?’. And this is no hastle at all, not for ESB and not for our Oracle Database. We can use the utl_http-package or utl_dbws-package to call our web service endpoint and the procedure holding the pl/sql functionality will be scheduled using dbms_job.

This demo and presentation gave me and the customers a real practical hands-on experience of Oracle’s Enterprise Service Bus and convinced us of the ease of integration when using such technology.

A pragmatic approach and an open vision using practical demo’s and hands-on sessions is the answer to every Enterprise Integration Challenge.

OWB Paris : What happened to “Generate intermediate results”???

Prior to OWB Paris, you could easily trap the SQL code that was being generated by OWB in a mapping : you simply had to rightclick on an outgoing group and select “Generate Intermediate Result”.

At first sight, this option disappeared in OWB 10G release 2, but this is not the case… Now, how can you get an intermediate result in OWB Paris? It’s simple … If you know it.

1. Select Generate from the top bar.

2. The Generation Results window will appear.

3. From the dropdown list, select “Intermediate” instead of “Full”.

4. Finally, select the outgoing group of which you want to see the intermediate result.

Like I said : simple, if you know it … ;o)

Using AJAX within Oracle Application Express (part 2)

Most of the AJAX calls will require some parameters. Instead of using an application item for every parameter, I wrote some javascript and PL/SQL functions that will store all AJAX parameters as XML in 1 application item!

The same javascript as in my first blog, but some extra parameters and functions are added.

This javascript function allows you to add an extra parameter to the AJAX call

This PL/SQL function allows you to read out the same parameter in the AJAX call’s PL/SQL code

We can simply add some parameters to our AJAX call. The first parameter of the AddParam function, is the name of the parameter (you must use the same name in your AJAX call’s PL/SQL code to read the parameter’s value), the second parameter is the parameter’s value.

In the PL/SQL code of the AJAX call, you can use the GetParameter function to read out these parameters

The result would be…

Securing an ESB Service using OWSM

Recently I was organizing a seminar regarding Oracle Soa Suite and the focus lied on Securing web services and integration.

In my demo I wanted to show the different features of BPEL, ESB and OWSM and of course the ease of integration of these different technologies.

The ame was to secure as well existing web services as the ESB services itself which are called inside my bpel processes.

In this way I can call stand-alone BPEL processes, ESB services and web services or integrate these technologies with eachother when required.

The difficulty of this exercise lies in calling secured soap services inside an ESB Service. Using a gateway we can secure the ESB Services and BPEL Processes used throughout the organization. The external webservices we need to integrate with are secured as well, and these security requirements need to be met by the calling service, which in our case is an existing BPEL Process or an ESB Service.

First lets have a look on how to call a secured external webservice, a partnerlink, in a bpel process. The webservice expects user-credentials to be able to call the webservice, these credentials can be added to the secured webservice using by editing the partnerlink.

Go to the partnerlink and click ‘edit’, go to the 3d tab which is the ‘Property’ tab and add the needed parameters to your webservice call. These parameters will define that credentials are added to the header of the web service call and the username and password which are required in this header. The properties you need to add are shown in the printscreen, for each property you need to define the proper values.
Such as for the wsseHeaders property, you need to define ‘credentials’ to state that the header will contain user credentials. This is standard web service security we’re using.

To be able to add these same credentials to the external web service, which is invoked from an ESB Service now, we need to come up with another solution. In the demo the same integration exercise is defined, once integration of external web service with BPEL and once through integration of ESB Routing Service with a SOAP Service. The only difficulty in the ESB Service is, that we can’t add these properties in a declarative way to the ESB Service. Instead we need to work with transformations to be able to add the credentials to the SOAP Service call.
How can we accomplish this, by adding parameters to the xsl-stylesheet which is used to transform the call that’s being made to the SOAP Service.

Have a look at the screenshot below to have a clear view on the needed parameters to add to the SOAP Service using an xsl-stylesheet in the transformation-property of our ESB Routing Service.

Using AJAX within Oracle Application Express

Everybody is already convinced about the strength and benefits of AJAX. There is only one thing that keeps bothering me when I’m using AJAX within Apex. For every AJAX call, you have to define a new application process.

I worked out a generic solution where you only need to create one application process for all your AJAX calls!
How to implement this generic solution
Step 1: Within Oracle Application Express
a) Create an application process and give it the name AJAX_DYNAMIC_PROCESS

b) Create an application item with the name AJAX_PROCEDURE

Instead of writing your PL/SQL code for the AJAX call in the application process, you have to write it on the database as a procedure (or a package procedure) Ex: pck$ajax_examples.prc$first_example

Step 2: Javascipt

Now we can put (by using javascript) the name of this procedure in the application item AJAX_PROCEDURE. The execute-immediate will execute the pck$ajax_examples.prc$first_example procedure.

I have written some javascript functions that simplify the use of AJAX:

Now implementing an AJAX call becomes as easy as this…

The ajaxResult variable will contain the result of your AJAX call!

This method is only recommended in an inTRAnet environment because your database is open to SQL Injections. You can also build in extra security, for example by checking your AJAX call againt a database table to make sure the call is permitted.

In a next blog I will explain how you also can generalise the passing of params to the Ajax call.