Data Integration Services – Consolidate your data (sources)

When talking about data integration services and depending on the audience you’re talking to, this could be:

  • Data needs to flow between different applications, instead of persisting the data on different data containers => Data Consolidation, Data messaging
  • Extract, transform and load => data warehousing, data consolidation
  • Services that are offered within an enterprise, or enterprise-wide, to share data => web services, messaging services, Service Bus, …

In other words, data integration services can hold a number of key performance indicators which are defined by the business metrics and requirements.

Besides the integration-aspect, the data-aspect is even more crucial such as defining the Enteprise Information Model so every consumer perceives the data in the same manner.

In regards to this aspect and the retail-sector, there’s an interesting blog-post regarding the Oracle Retail Data Model (ORDM).

OWB 11G ( – Overall Goal, new features, installation guidelines

The overall goal :

Integrate the OWB technical stack with the 11g database. This simplifies the OWB installation by incorporating it as a database option during database installation.

Additional goals include:

  • Not requiring SYSDBA credentials at OWB installation time
  • Requiring only one OWB schema per Oracle Database instance
  • Requires only a single Control Center Service for the database instance, serving the OWBSYS schema.
  • The single unified repository enables maintaining a single copy of OWB database objects in OWBSYS (tables, views, PL/SQL packages, and so on).

Features (also available in the OWB patch):

  • Versioning of Type 2 SCDs: Hierarchy versioning supports multiple hierarchies. Hierarchy versioning is not enabled by default for type 2 SCDs (slowly changing dimensions). You must use the Data Object Editor to enable hierarchy versioning.
  • Set-based DELETE in Mapping Code Generation: You can design a mapping with the loading type set to DELETE and code generation set to SET BASED.
  • Merge Optimization for Table Operators: You can enable the Merge Optimization property for table operators. When set to true, this property optimizes the invocation or execution of expressions and transformations in the MERGE statement.
  • DML Error Logging to Handle Error Tables: DML error logging is available for set-based maps.

When Do You Need Stand-alone Installation?
The OWB 11g stand-alone installation is required only if you must:

  • Deploy to an Oracle Database 10g, release 2 target
  • Perform Discoverer deployment

The stand-alone DVD is bundled with the 11g database pack.

Debugging Bpel processes

You could debug bpel processes using different kinds of approaches, such as looking into audit trails, sniffing the soap envelopes with the request- and response message, using junit, using the bpel console, …

In this post I’ll mention the 2 approaches I use the most, namely the bpel console itself and Junit.

Visual Debugging Using BPEL Console

Oracle Enterprise Manager 10g BPEL Control reduces the cost and complexity of deploying and managing your business processes. Visually monitor the execution of each BPEL process, drill down into the audit trail and view the details of each conversation, or debug a running flow against its BPEL implementation.

If you’ve deployed a bpel process, you can test the execution in the BPEL Console: http://server:port/ BPELConsole.

In the screen above you can see the deployed bpel processes on the lef-hand side of the screen. To instantiate such a process and create a test instance you can click on the process name and the below screen will be shown:

In this screen you can test the process by defining your own payload, data to be processed by the BPEL process. To define the payload you can use an html-form, the default screen or you can paste the soap-envelop, an xml-based message into the xml-source textarea. To actually test the instance you just need to click on the ‘Send XML-Message’-button. You can also perform stress tests on the bpel processes to verify if performance problems may occure on peak moments.

When you’ve clicked on the button, the process is instantiated and the following screen is shown:

In the tabbed windows you can have a detailed look at the instantiated process, depending on your requirements:

Visual flow:

The activities that failed, threw an exception, are shown with a red background. Each activity in the visual flow holds all needed information for that specific activity. When you double click an activity, the needed data will be shown in an xml-format, because xml is the standard messaging-format for web services.

Audit instance:

Debug instance:

Debug BPEL Processes via JUnit

As soon as a BPEL process is deployed, the BPEL process lives on as being a web service. The webservice can be accessed by its endpoint, or wsdl location.

On the wsdl-tab of your BPEL Process, in the Bpel Console, you can look-up the end-point of the deployed bpel process = web service.

In Jdeveloper you can define a Web Service Proxy and integrate a Junit-test case for this web service proxy:

package test.proxy;

import javax.xml.rpc.ServiceFactory;

class BPELTest_ServiceTest extends junit.framework.TestCase{
BPELTest_Service myBPELTest_Service;

BPELTest_ServiceTest(java.lang.String name){

protected void setUp() throws Exception {
ServiceFactory factory =
myBPELTest_Service =

protected void tearDown(){
myBPELTest_Service = null;

public void testBPELTestPortprocess() throws java.lang.Exception {
test.proxy.BPELTest_PortType port = myBPELTest_Service.getBPELTestPort();
test.proxy.BPELTestProcessRequest payload = null;
BPELTestProcessResponse response = port.process(payload);
assertEquals(“389”, response.toString());

Force page submits when paginating via selectlist

This is an extension for my previous post, how to not only submit via the next and previous links, but also via select.

This might not be the best solutions, and improvements are possible, but it’s a good startpoint. We will replace the #TEXT#-variable of Apex with our own generated html-code.

You need to add 3 extra lines in your sql-query to get the information you need, these columns don’t need to be displayed:

You will need to create a function that create our html-code, and an application-item to store the result in. My app-item will be API_SELECT and my function will be f_get_report_vars. The code will be shown below.

Next we have 2 modification in the report template.

We need to retrieve our total number of rows, our current displayed rows and our rows per page. I changed the column template 1 condition to PLSQL and added f_set_reportvars(‘#API_ROWNUM#’,’#API_TOTALROWS#’,’#API_RPP#’) = 0.

0 is a dummy value so my evalution is always TRUE. It might not be the correct place to do this, because it wasn’t designed to be used like this and the function is execute for every displayed row, instead of just once. (Suggestions are welcome)

Next step is changing the pagination template, which is default empty or
&ltspan class=”instructiontext”>#TEXT#&lt/span>
and use our app-item
&ltspan class=”instructiontext”>&API_SELECT.&lt/span>

And the function making this magic happen would be:

create or replace
FUNCTION f_set_reportvars(pin_rownum IN NUMBER
,pin_total_rows IN NUMBER
,pin_rpp IN NUMBER
lv_select VARCHAR2(1000);
ln_from NUMBER;
ln_to NUMBER;
ln_sets NUMBER;
ln_page_id NUMBER := apex_util.get_session_state(‘APP_PAGE_ID’);
ln_app_id NUMBER := apex_util.get_session_state(‘APP_ID’);
ln_session NUMBER := apex_util.get_session_state(‘SESSION’);
ln_region_id NUMBER;
–optional, set variables for other usage

–get region id, when more then one report per page you need a tag in your static_id
SELECT region_id
INTO ln_region_id
FROM apex_application_page_regions
WHERE application_id = ln_app_id
AND page_id = ln_page_id
AND source_type = ‘Report’
/*AND static_id LIKE ‘MYTAG%’*/;

–define number of row ranges
ln_sets := CEIL(pin_total_rows/pin_rpp);
–when more then 1 row range, create select list
IF ln_sets > 1 THEN
lv_select := ‘&ltselect id=”X01_’||ln_region_id
||'” onchange=”doSubmit(”f?p=’||ln_app_id||’:’||ln_page_id||’:’||ln_session
||’:pg_R_’||ln_region_id||’:NO&pg_min_row=”+this.options[selectedIndex].value+”&pg_rows_fetched=_’||pin_rpp||”’);” size=”1″ name=”X01″>’;
–make an option in the select list for every row range
FOR i IN 1..ln_sets LOOP
ln_from := ((i-1)*pin_rpp)+1;
ln_to := LEAST((i*pin_rpp),pin_total_rows);
–make difference between current row range and others
IF pin_rownum BETWEEN ln_from AND ln_to THEN
lv_select :=lv_select||’&ltoption selected=”selected” value=”current”>Row(s) ‘||ln_from||’-‘||ln_to||’ of ‘||pin_total_rows||’&lt/option>’;
lv_select :=lv_select||’&ltoption value=”‘||ln_from||’&pg_max_rows=’||pin_rpp||'”>’||ln_from||’-‘||ln_to||’ of ‘||pin_total_rows||’&lt/option>’;
–close select-tag
lv_select := lv_select||’&lt/select>’;
–set application item with select list
apex_util.set_session_state(p_name => ‘API_SELECT’
,p_value => lv_select);
–return dummy

How to handle logging in BPEL Processes

1.1. Logging In Bpel

Logging can be performed on domain-level and system-level and you can use different mechanisms to log events, task details, …

In this post I’ve summarized the basic logging functionality you can use on bpel processes.

1.1.1. Process Logging Information

Oracle BPEL Process Manager uses the log4j tool to generate log files containing messages that describe startup and shutdown information, errors, warning messages, access information on HTTP requests, and additional information.

The log4j tool enables logging at runtime without modifying the application binary.
Instead, logging behavior is controlled by editing properties in Oracle BPEL Control
and Oracle BPEL Admin Console.

Two logging levels are supported in Oracle BPEL Process Manager:
· Domain
o Manages logging information within specific domains
· System
o Manages logging information on a system-wide level Domain-wide Logging

These can be configured through the BPEL Console (http://hostname:port/BPELConsole) > manage BPEL domain > logging or by editing log4j-config.xml in $BPEL_HOME\integration\orabpel\domains\\config

The different domains to log about:
· .collaxa.cube.engine.deployment – deployment related logging
· .collaxa.cube.compiler – compilation related logging
· .collaxa.cube.messaging – messaging layer (as bpel used messaging services to scale)
· – server side security (fwrk)
· – inside validator logging
· – everything that is related to communication (WSIF layer, SOAP, Adapters) – shows you at least a longer stack if something breaks there
· .collaxa.cube.xml – xml transformation, storage, hydration
· – logging for services like Notification or Human Workflow
· – Delivery Service and Manager, responsible for callbacks, and first (initiating) delivery
· .collaxa.cube – cube related logging (system) System-wide Logging

System-wide loggers are used for logging information about infrastructure, AXIS and WSIF bindings. They can be configured through the BPEL Admin Console (http://hostname:port/BPELAdmin) > logging or by editing log4j-config.xml in $BPEL_HOME\integration\orabpel\system\config

The different systems to log about:
· org.collaxa.thirdparty.apache.wsif – logger for system-wide WSIF
· org.collaxa.thirdparty.apache.axis.transport – logger to see what axis is sending on the wire
· org.collaxa.thirdparty.apache.axis – general axis related logging
· – all BPEL PM wide services
· collaxa.cube.infrastructure – infrastructure such as DB connectors Log Level

The following logging levels are available and listed here from highest priority to lowest priority. When a logging level is specified, all messages with a lower priority level than the one selected are ignored.

· Off
o Disables logging. This selection is the highest priority.
· Fatal
o Logs critical messages. After logging occurs, the application quits abnormally.
· Error
o Logs application error messages to a log; the application continues to run (for example, an administrator-supplied configuration parameter is incorrect and you default to using a hard-coded value).
· Warn
o Logs warning messages to a log; the application continues to run without problems.
· Info
o Logs messages in a format similar to the verbose mode of many applications.
· Debug
o Logs debugging messages that should not be printed when the application is in a production environment.
· All
o Enables all logging. This selection is the lowest priority.

1.1.2. Logging with Sensors

You can use sensors to generate application logging activity.

Note that logging with sensors impacts performance because sensor data objects are built even when logging is disabled.

You add sensors to specific activities and then extract data from variables. To do this, you must implement a custom sensor publishing action to do the log4j logging. For example, you can create a sensor on an invoke activity and create a message that is
sent to a JMS queue.

1.1.3. Logging with bpelx:exec in a Java Embedding Activity

You can also log messages by adding custom Java code to a BPEL process using the Java BPEL exec extension bpelx:exec inside a Java Embedding activity in Oracle JDeveloper.

The method addAuditTrailEntry(String):void enables you to add an entry to the audit trail.

Force page submits when paginating through a report

How do you force a page submit when paginating through a report?

Standard behaviour from apex is to redirect when navigating through the recordset of a report. While this is advisable in most cases, we sometimes need processes to run when the user clicks the next or previous button.

I have often seen the question, but never an answer. Although this can be achieved fairly easy, with following steps.

1) Adapt the report template. Best is to create a new report template, based on an existing template, so we don’t affect all pages.
I copy “Standard Report” and name it “Standard Report with Submit”.

2) In this new template we are interesting in the “Pagination Subtemplate”. We change “Next Page Template”, the original value will be a redirect:
&lta href=”#LINK#”>next&lt/a>
We can easily change this to a submit:
&lta href=”javascript:doSubmit(‘#LINK#’);”>next&lt/a>

And we change “Previous Page Template” analogous from :
&lta href=”#LINK#”>previous&lt/a>
&lta href=”javascript:doSubmit(‘#LINK#’);”>previous&lt/a>

3) When we use this report template on our page, the next and previous button will submit, and the request will be the url it wants to redirect too. This way we can use our ‘after submit’-processes.

4) The last step we need is to foresee a branch.
We create a branch to URL, and our url will be our request:
We make the branche conditional, based on Pl/Sql:

Steps 3 & 4 need to be repeated for every page needing this functionality. Our processes can be made conditional in the same manner as our branch.

We used this for saving our data temporarily to a collection when paginating through an updatable report, and we didn’t notice any drawbacks using this method.

JSF/ADF and the browsers’ back button

When you’re developing a JSF or ADF Faces application and your giving your customer a first testing-experience, you’ll notice the browser’s back button is a very interesting functionality used by a lot of end-users.

JSF saves the state of every page loaded in a browser, which means everytime a user clicks the browser back button, JSF loads the saved state of the target page.

You will also notice that the application is behaving very weard and unpredictable and you as a developer will need to solve the problem.

Possible solutions:

  1. Integrate the needed java script functionality to display the application in a full screen window (back button isn’t displayed anymore)
  2. Define no-caching on your web application using the phaselistener (jsf api’s)
  3. The user needs to refresh the page he ‘backed to’ => maybe this isn’t an option when I’m talking about developers and customers ;o)
  4. Use ‘enableTokenValidation=false’ within an ADF Application
  5. Define the needed state- and session-parameters in your jsf’s configuration file, read following post

I still think, when talking to collegues, browsing the internet/communities etc., you need to tell the customer that when using the back button, unexpected behaviour can occure in the application. Using bread-crumbs, task-oriented applications, separate crud-pages, etc. the customer won’t be that easily temped to use the back-button.