Migrate Existing ODI from DEV to TEST to Production

In the previous posts on this Blog you’ve read about my experiences with Oracle Data Integrator and now a new important milestone was reached … import and export the ODI interfaces, datastores, … from DEV-environment to TST-environment.

I’ve asked on OTN what the best way was to accomplish this and different views were given on how to solve this.

You could use import/export functionality from ODI itself, e.g. if you want to partially export a certain interface or datastore, you could export a specific object.
If you want to migrate from DEV-environment to TEST, you could use the import/export functionality from the Oracle Database because my master- and work-repository are stored in an Oracle 10g Database.

I needed to move my development environment onto the test-environment to be able to run the ODI functionality against real-time test data to be able to test the performance more accuratly. Another important raison also was that my development environment, my notebook, had crashed a couple of times already so I needed to back-up all the important data.

What did I do to accomplish this import/export:
- I exported the master- and work repository used by ODI (snpw and snpm-schema’s)
- I copied over the ODI-folder from my program-files folder, just to make sure

After backing-up all other stuff on my laptop, I needed to format my laptop and have a closer look at the problems I was facing concerning my hard-drive etc.

Alea iacta est

A new laptop, a new environment … let’s start installing all needed software and then the time had come to get back up and running with my ODI environment.

First I tried to import the existing Master Repository but the wizard didn’t really guide me a lot during this process so another approach was needed.

At last, the successfull steps to import/export from environment 1 to environment B such as DEV to TEST are the following:

  • Export existing master- and work-repository in a dump-file using Oracle db export feature
  • Import the master- and work-repository into your new schema
  • Create a new connection to the imported master-repository when you connect to the Topology Manager in ODI
  • Create a new connection to the imported work-repository when you connect to the Designer manager in ODI

Now your work is done … if you check out the model- and projects-tab in your Designer-view you will notice all interfaces, datastores, models, etc. are imported successfully.

Errors when trying to invoke an ESB Service from BPEL or UI

When you try to invoke an ESB Service from a BPEL Process or from an ADF application you can run into the following error/exception:

Error happened when reading wsdl at “http://localhost:8888/esb/wsil/Fulfillment/OrderFulfillment?wsdl”, because “Failed to read wsdl file at: “http://localhost:8888/esb/wsil/Fulfillment/OrderFulfillment?wsdl”, caused by: java.net.ConnectException. : connect: Address is invalid on local machine, or port is not valid on remote machine”.

What does this error mean, and how can you solve it?
When the Bpel Process or UI tries to access the ESB Service but can’t find the webservice or wsdl these exceptions will be thrown. The first step to perform is to check the connection properties in your Esb Control such as shown in the screen below:


Make sure the VirtualHost and Port are defined correctly, afterwards you can check the Routing Service itself to make sure the endpoint is defined correctly.

ApEx: Yellow Text Items

Today I came across a strange problem, I received an email of a customer with feedback about an application and the first line was: “Layout is nice but I don’t like the fact that certain text items have a yellow background”. I opened the application looking for yellow text items but I couldn’t find anything yellow so I emailed my customer for a screenshot:



Seeing the yellow text item I first inspected my items looking for something out of the ordinary but I couldn’t find anything special. After searching the internet for some time, I found finally an explanation. A user with the exact same problem posted that he had “google toolbar” causing the problem.

Seeing the screenshot above, I noticed that google toolbar was active. I then phoned my customer with the question for a screenshot without google toolbar:

My problem was found, the browser in question is IE 7.0. After some digging I came across code.jenseng.com where a workaround is explained. I will try some of their suggestions in the next couple of days…

Create a demo using EJB 3.0, Toplink and ADF as the UI layer and Bpel and ESB as the back-end layer (through web service invocation) – Episode 1

The case I worked out for my demo application is the following: Create a new person via the UI (using JSF and EJB3.0 as data-layer) and initiate a bpel process for the creation of the person. The bpel process will check if all business requirements were met for the person that needs to be created using Business Rules.
A human task was added to make sure the Personal Manager has approved the new person and finally an ESB was added to actually create the person.
The ESB will transform the person-object, an xml-file, to the specific format I need to be able to insert the person in my db.

During the creation of my demo-project I faced some design problems which I will explain in the following chapters:

The first part of the case was simple, create a UI using EJB 3.0 and ADF Faces using JDeveloper as my IDE.
The UI consists of a ListPersons.jspx – page that lists all existing persons of my persons-db and a link to the CreatePerson.jspx-page. In the CreatePerson.jspx page I will create a new Person and initiate the bpel process from here.

But it wasn’t as simple as I thought … to be able to initiate my bpel process using the EJB-objects I needed to prefetch the sequence-value needed for uniquely identifying the person-object, e.g. the Person.personid. This is needed because the bpel process needs all xml-tags to be filled in. If for example the personid, or firstname isn’t filled in you will get the following exception ‘unexpected null value for literal data’.

To be able to prefetch the id in my EJB i thought I could ‘eagerly fetch’ this id using an annotation in EJB 3.0, but there’s no such annotation available :(

What to do next … fetch the sequence value myself and populate the Person.personID with my sequence-value.

I’ve added a new method in my SessionBean that gets the sequence value from my db-sequence, this method is invoked from my custom-method ‘createPersonObject() which constitutes a valid person-object.

The custom-method to fetch the sequence-value uses the ‘createNativeQuery’-method on the EntityManager:

em.createNativeQuery(“select person_seq.nextval from dual”);

The UI now works correctly, using the createPersonObject()-method as binding-layer to go to the CreatePerson.jspx file.

Now I need to link my existing Bpel Process to this UI … coming up soon …

Solution for page refresh problems in ADF when no relationships are used.

I have a small program which is used for registering money transfers.

It has two tables, table A holds all the transfer information and table B keeps the total amount of money earned so far. There is no connection between the tables, there are only triggers on table A which keep table B up to date.

On the overview page it looks like this:

The problem that I had was that when I pressed “Create” and inserted a new record, the total amount field was not updated after the create page navigated back to the overview page. It didn’t refresh, but the trigger was called and executed. So, for example, after “2 very large bags of potatoes” was inserted the “Total Amount” field would still display 10, although if you peeked in table B it would say 30.

I don’t know if it’s the right solution, but I solved it this way:

In the page definition of the overview page I made a new action, an execute action based on the iterator of table B.

<action IterBinding=”TotalAmountIterator” id=”ExecuteTotalAmount”
InstanceName=”SampleServiceDataControl.TotalAmount”
DataControl=”SampleServiceDataControl” RequiresUpdateModel=”true”
Action=”2″/>


To invoke it every time the page is loaded, you do this:

<invokeAction Binds=”ExecuteTotalAmount”
id=”ExecuteTotalAmountOnPageLoad”
Refresh=”always”
RefreshCondition=”#{!adfFacesContext.postback}”/>

So far the Total Amount field will always be refreshed when the application navigates to the overview page.

There is only a small exception, the delete button. The effect of pressing the delete button – besides the deletion of the record – is a partial page refresh, so the main table is OK, but the total amount field is not.

I fixed it by calling the method I declared in the page definition in the deleteButton method in the backing bean of the overview page.

public String deleteButton_action() {
BindingContainer bindings = getBindings();
OperationBinding operationBinding =
bindings.getOperationBinding(“Delete”);
Object result = operationBinding.execute();

operationBinding = bindings.getOperationBinding(“Commit”);
result = operationBinding.execute();

operationBinding = bindings.getOperationBinding(“ExecuteTotalAmount”);
result = operationBinding.execute();

return null; }