Migrate your MS Access data to an Oracle database using the ETL Tool Talend

APEX is promoted as the perfect replacement for MS Access applications. One thing you should consider though is how you migrate your data to the Oracle database. In APEX there is a handy tool called the Data Workshop that can be used for this. You first export your Excel files from the MS Access database, and then follow the data upload wizard to import the data into identical tables. Since you are not always working with a 1-1 relationship, you will most likely have to write some PL/SQL to get all the data in the right tables.

dataworkshop

The downside is that you will need to repeat this process when you go into production. This is not a big problem if you only have one table to migrate. But if you have multiple tables and/ or your users also want new data during tests and trainings, you will spend a lot of time exporting and importing Excel files.

A recent APEX project for a client required a large data migration from MS Access Databases to the Oracle database. Because we would require fresh data on several points in the development process we decided to use the ETL Open Source Tool Talend. We got impressed of how intuitive the tool is, it only took a few days before we were familiar with the tool. Once you get the hang of it, you can write (or should I say draw) migrations of tables in no time. We needed to migrate from an MS Access database but the tool supports a wide range of databases and documents to import your data from. In total we migrated around 30-40 tables to our Oracle database.

Let’s have a closer look at one of our migration jobs.

talendmsaccessoracle

At the left we see our MS Access database. Each tAccessInput component will get data from one table. After that we join the tables in our tMap_1 component. The reason we don’t just write our joins in one component, is because this way we can really see how many rows every table returns.

On the bottom we have some Oracle Database input connections. They will join the persons of our MS Access Database with the persons in our Oracle Database based on the National registration number. After that we write our data to our Oracle Database. You may notice that we have two lines going to Excel files. This is our error logging; we use this to log the rows that did not find a match. In our first Excel for example we write persons that did not find a match in our Oracle Database.

This is just one example, in total about 20 jobs were built. During the development we also had to deal with certain calculations or convert data. For most things there was a component ready to use and if there wasn’t you could always write a Java expression in the tMap items.

I hope I convinced you of the benefits of using Talend as a migration tool for APEX projects, because we will certainly use this tool again!

OOW 2010: Moving forms to ADF

When working with Oracle Forms these days and you’re not satisfied with the application anymore, there are some possibilities you can do:

  • upgrade
  • modernize
  • integrate
  • migrate

On our OOW session tomorrow(Oracle Forms in the Middle of Middleware, 1pm, Marriott Marquis Room: Salon 9), we will talk about the first three possibilities, upgrade, modernize and integrate.

But today I went to the session of Grant Ronald: Moving from Oracle Forms to Java and Oracle Application Development Framework
A session about migrating Oracle Forms to ADF.
The strategy of oracle is NOT desupporting Oracle Forms, on the contrary, they’re working on new features for 11g R2.

But when you consider migrating, do it for the right reasons.
Three kinds of reasons: the good, the bad and the ugly

Reasons to choose for migration can be

  • forms doesn’t meet the requirements anymore
  • there’s need for re-development
  • adopt leading edge, modern technologies

Reasons NOT to choose for migration:

  • there’s a heavy forms investment you don’t want to throw away
  • happy with data entry (and to my opinion forms is one of the best choices for data entry applications)

Wrong reasons:

  • forms will be desupported -> A clear answer of Grant Ronald: THIS IS NOT THE CASE!
  • upgrading your forms application will result in big problems
  • rewriting the application will save $$$

So migration is an option for your forms application, but Grant stated it several times in his session: DO IT FOR THE RIGHT REASON.

About migrating forms to ADF…
The technologies look similar…
Grant made a comparison between a dish washer and a washing machine.
Both have the same measurements, do similar things(wash something and dry it), etc.
But who puts his clothing in a dish washer?  Or cups and glasses in a washing machine?
So thechnologies look similar, but are different:

  • Java applet <> HTML/javascript
  • PL/SQL <> Java
  • Stateful <> stateless
  • No separation of UI and data elements <> seperate UI and data elements

Do not ignore those differences when looking at migration!

ADF is a framework and does a lot of things for you(like log on to the database, you don’t have to write the code) which is pretty nice.
But hey, Forms does also things for you, it’s also a framework.

You can build applications in ADF that look like forms application and have the same behaviour, but is that the reason to migrate, to work the same way?

When migrating there are some more challenges, eg reusability of table/views, procedures/functions, PLL, triggers.  What about forms built-in functions?

So, of course migration is an option for your Oracle Forms application, but ask yourself a question: Why migrate?
Take a look at all the options, before going to migrate.  It’s not an easy path to walk…

Check also the paper Grant wrote about migrating: Migrating Oracle Forms to Fusion: myth or magic bullet