Thursday, May 31, 2007

Access Migration Tutorial

Are you considering migrating that Microsoft Access application to Oracle Application Express? Well read on...

Hopefully you are aware of Oracle SQL Developer and that we have redeveloped the Migration Workbench and integrated it tightly with Oracle SQL Developer. An early adopter version of this is available now and will be production very soon. More of that in a subsequent post.

With Oracle Application Express 3.0 we introduced the Application Migration Workshop to assist with migrating your Access Forms & Reports. When I talk about this solution, I get asked do we have a step by step guide or methodology for such migrations. So, we have produced a migration tutorial to address this and have published it on OTN.

We have taken the Microsoft Access sample application, Northwind Traders and migrated it to Oracle Application Express. The tutorial covers this in step by step detail. Following this tutorial would be a useful exercise for any user that wishes to undertake migrating their applications from Microsoft Access to Oracle Application Express. We have called the converted application Southwind Wholesalers. :-)

You can see it running on apex.oracle.com and we have also provided it as a packaged application so you can examine it in detail.

Thursday, May 17, 2007

Updated Oracle SQL Developer Migration Workbench Early Adopter Release

We have updated the early adopter release of Oracle SQL Developer Migration Workbench today on OTN. You can get it from here. This is our final preview release, before we go production. We are now functionally complete for this initial production release and are now focused on fixing our final "show stopper" bugs. We have had good feedback from our user community via our feedback application and also via the Workbench forum. They have uncovered a number of bugs, most of which we have now addressed (Oracle9i as a repository and Access data migration issues for example) and the remaining ones we will resolve prior to production. I encourage everybody to update to this latest release and continue to provide us with feedback.

I have been working extensively with different builds of the Migration Workbench these past couple of weeks as we closed in on our goal to refresh the early adopter version. In my "biased" opinion is it looking much stronger and I would like to outline some of the new features in this updated early adopter release.

Quick Migrate
In the orginal Migration Workbench we had a wizard driven approach to simplify migrations and I felt it was important to bring this functionality back. With our Quick Migrate wizard, I believe we have improved from the original wizard, since we will leverage our least priviliege migration capabilities, assume sensible defaults and create/remove our migration repository.



So if you have a schema on SQL Server or a single Access mdb file to migrate to an existing Oracle schema, this should be the easiest and quickest migration option for you. Another nice feature, if you are doing an access migration, is that we have added command line support to our exporter so, we will automatically launch the correct Access exporter for the Access connection that you specify.

Offline Capture
This was a popular feature with our consultants and partner technical services folks, with the original Workbench, as it allowed them to work remote from the customer/partner. We have now added back in that feature.

Migration Reports
We have added in some initial migration reports available under Reports->Shared Reports. This will be an area we will add to into the future, as we can mine our rich metadata repository to provide you with useful information. If you have suggestions for additional reports let me know. I will also publish more details about our repository, so you can develop your own migration reports as well. Maybe we should have a competition for the best contributed report? I think we have a couple of 1GB USB keys left over from our Database Developer Day in Dublin I could use as prizes.

Translation Scratch Editor
We have reworked this feature extensively. I originally wanted to add a feature that would enable you to validate our translated SQL. As we worked through different iterations of how best to implement this feature, we came up with the idea about leveraging our existing Worksheet capabilities, which I think is very cool and I am very pleased with how this turned out.


We have also done a lot of work to improve incremental capture and improve our filtering capabilities from our early adopter release. We have integrated our MySQL parser from the original Migration Workbench and will extend its capabilities in subsequent releases to be as functional as our new TSQL parser and also support SQL statement level translation. (workaround for now, within the scratch editor, is just wrap the SQL statement in a procedure). We have also implemented the ability to update your Access mdb file, to create link tables to point to your newly migrated schema. This was also a feature of the original workbench. We hope to add some additional usability tweaks to create an ODBC OSN on the fly and provide a select list of known Oracle DSN. Hopefully that will make it in before production as well.

We have made fixes to ensure correct generation order for pl/sql procedures to resolve dependencies, so more pl/sql procedures should compile correctly first time. We made improvement to handle inline DDL statements correctly. Temporary tables, normal tables and other DDL are lifted out of the body of the procedure/function and are created separately.

Now for the final bug fix push by the development teams in Dublin and Bangalore. Our QA team, have been doing a good job verifying our fixes and closing off our bugs. The teams have been working hard on this for many months now and I believe we are in touching distance of reaching our goal. It will be very exciting for me personally to see this second generation migration tool reach production. We'll all need some time off when this is done to recharge...